[Home] [Help]
PACKAGE BODY: APPS.QP_LIST_UPGRADE_UTIL_PVT
Source
4 Procedure upgrade_flex_structures is
1 package body qp_list_upgrade_util_pvt as
2 /* $Header: QPXVUPLB.pls 120.1 2006/03/21 11:17:25 rnayani noship $ */
3
5 err_num NUMBER;
6 err_msg VARCHAR2(100);
7 BEGIN
8
9 QP_UTIL.QP_UPGRADE_CONTEXT('OE', 'QP', 'SO_PRICE_LISTS', 'QP_LIST_HEADERS');
10
11 QP_UTIL.QP_UPGRADE_CONTEXT('OE', 'QP', 'SO_PRICE_LIST_LINES', 'QP_LIST_LINES');
12
13 EXCEPTION
14
15 WHEN OTHERS THEN
16 err_num := SQLCODE;
17 err_msg := SUBSTR(SQLERRM, 1, 240);
18 ROLLBACK;
19 qp_util.log_error(NULL, NULL, NULL, NULL,
20 NULL, NULL, NULL, NULL,
21 'UPGRADE_FLEX_STRUCTURES',
22 err_msg, 'PRICE_LISTS');
23 RAISE;
24 END upgrade_flex_structures;
25
26
27 procedure insert_line_distribution
28 (
29 l_worker in number,
30 l_start_line IN Number,
31 l_end_line IN Number,
32 l_type_var IN Varchar2
33 )
34 is
35 Begin
36
37 insert into qp_upg_lines_distribution
38 (
39 worker,
40 start_line_id,
41 end_line_id,
42 alloted_flag,
43 line_type,
44 creation_date
45 )
46 values
47 (
48 l_worker,
49 l_start_line,
50 l_end_line,
51 'N',
52 l_type_var,
53 sysdate
54 );
55
56 end insert_line_distribution;
57
58
59 procedure create_parallel_lines
60 ( l_workers IN number := 5,
61 p_batchsize in number := 5000)
62 is
63 v_type varchar2(30) := 'PLL';
64
65 cursor lines
66 is
67 select
68 price_list_line_id
69 from
70 so_price_list_lines_115
71 where price_list_line_id not in ( select list_line_id
72 from qp_LIST_lines )
73 order by price_list_line_id;
74
75
76 l_total_lines number;
77 l_min_line number;
78 l_max_line number;
79 l_counter number;
80 l_gap number;
81 l_worker_count number;
82 l_worker_start number;
83 l_worker_end number;
84 l_price_list_line_id number;
85 l_start_flag number;
86 l_total_workers number;
87
88 Begin
89
90 delete from qp_upg_lines_distribution
91 where line_type = v_type;
92
93 commit;
94
95 begin
96 select
97 count(*),
98 nvl(min(price_list_line_id),0),
99 nvl(max(price_list_line_id),0)
100 into
101 l_total_lines,
102 l_min_line,
103 l_max_line
104 from
105 so_price_list_lines_115
106 where price_list_line_id not in ( select list_line_id
107 from qp_LIST_lines );
108
109 exception
110 when others then
111 null;
112 end;
113
114 if l_total_lines < p_batchsize or l_workers = 1 then
115
116
117 insert_line_distribution
118 (
119 l_worker => 1,
123 );
120 l_start_line => l_min_line,
121 l_end_line => l_max_line,
122 l_type_var => 'PLL'
124
125 else
126 l_max_line := 0;
127 l_min_line := 0;
128 l_total_workers := l_workers;
129 l_counter := 0;
130 l_start_flag := 0;
131 l_worker_count := 0;
132 l_gap := round(l_total_lines / l_total_workers, 0);
133
134 for lines_rec in lines loop
135
136 l_price_list_line_id := lines_rec.price_list_line_id;
137 l_counter := l_counter + 1;
138
139 if l_start_flag = 0 then
140 l_start_flag := 1;
141 l_min_line := lines_rec.price_list_line_id;
142 l_max_line := NULL;
143 l_worker_count := l_worker_count + 1;
144 end if;
145
146 if l_counter = l_gap and l_worker_count < l_total_workers
147 then
148 l_max_line := lines_rec.price_list_line_id;
149
150 insert_line_distribution
151 (
152 l_worker => l_worker_count,
153 l_start_line => l_min_line,
154 l_end_line => l_max_line,
155 l_type_var => 'PLL'
156 );
157
158 l_counter := 0;
159 l_start_flag := 0;
160
161 end if;
162
163 end loop;
164
165 l_max_line := l_price_list_line_id;
166
167 insert_line_distribution
168 (
169 l_worker => l_worker_count,
170 l_start_line => l_min_line,
171 l_end_line => l_max_line,
172 l_type_var => 'PLL'
173 );
174
175
176 commit;
177 end if;
178
179 end create_parallel_lines;
180
181
182 PROCEDURE CREATE_PARALLEL_SLABS
183 ( L_WORKERS IN NUMBER := 5)
184 IS
185 V_TYPE VARCHAR2(30) := 'PLL';
186
187
188 L_TOTAL_LINES NUMBER;
189 L_MIN_LINE NUMBER;
190 L_MAX_LINE NUMBER;
191 L_COUNTER NUMBER;
192 L_GAP NUMBER;
193 L_WORKER_COUNT NUMBER;
194 L_WORKER_START NUMBER;
195 L_WORKER_END NUMBER;
196 L_PRICE_LIST_LINE_ID NUMBER;
197 L_START_FLAG NUMBER;
198 L_TOTAL_WORKERS NUMBER;
199
200 BEGIN
201
202 DELETE from
203 QP_UPG_LINES_DISTRIBUTION
204 where line_type = v_type;
205
206 COMMIT;
207
208 BEGIN
209 SELECT
210 NVL(MIN(PRICE_LIST_LINE_ID),0),
211 NVL(MAX(PRICE_LIST_LINE_ID),0)
212 INTO
213 L_MIN_LINE,
214 L_MAX_LINE
215 FROM
216 SO_PRICE_LIST_LINES_115;
217
218 exception
219 when others then
220 null;
221 end;
222
223
224 FOR I in 1..l_workers loop
225
226 l_worker_start := l_min_line + trunc( (i-1) * (l_max_line-l_min_line)/l_workers);
227
228 l_worker_end := l_min_line + trunc(i*(l_max_line-l_min_line)/l_workers);
229
230 if i <> l_workers then
231
232 l_worker_end := l_worker_end - 1;
233
234 end if;
235
236 qp_modifier_upgrade_util_pvt.insert_line_distribution
237 (
238 l_worker => i,
239 l_start_line => l_worker_start,
240 l_end_line => l_worker_end,
241 l_type_var => 'PLL'
242 );
243
244 END LOOP;
245
246 commit;
247
248
249 end create_parallel_slabs;
250
251
252
253 Procedure create_price_list(p_batchsize in number := 5000,
254 p_worker in number := 1) is
255
256 l_context varchar2(30) := NULL;
257 l_attribute varchar2(30) := NULL;
258 l_qualifier_grouping_no NUMBER := 0;
259 l_sec_qualifier_grouping_no NUMBER := 0;
260 err_num NUMBER;
261 err_msg VARCHAR2(100);
262 l_sysdate date;
263 l_context_flag VARCHAR2(1);
264 l_attribute_flag VARCHAR2(1);
265 l_value_flag VARCHAR2(1);
266 l_datatype VARCHAR2(10);
267 l_precedence NUMBER;
268 l_error_code NUMBER;
269 v_errortext VARCHAR2(240);
270 l_price_list_id NUMBER; /* Renga */
271 l_list_header_id NUMBER := 0;
272 l_secondary_price_list_id NUMBER;
273 e_validate_flexfield EXCEPTION;
274 e_get_prod_flex_properties EXCEPTION;
275
276 l_header_count number := 0;
277 l_min_price_list number := 1;
278 l_max_price_list number := p_batchsize;
279 numiterations number := 1;
280 i number := 1;
281 j number := 0;
282 K number := 0;
283 agr_price_list_id number := 0;
287 new_price_list_id number := 0;
284 agr_count number := 0;
285 start_price_list_id number := 0;
286 end_price_list_id number := 0;
288
289
290 TYPE PRICE_LIST_ID_TAB IS TABLE OF QP_LIST_HEADERS_B.LIST_HEADER_ID%TYPE INDEX BY BINARY_INTEGER;
291
292 TYPE COMMENTSTAB IS TABLE OF QP_LIST_HEADERS_B.COMMENTS%TYPE INDEX BY BINARY_INTEGER;
293 TYPE CONTEXTTAB IS TABLE OF QP_LIST_HEADERS_B.CONTEXT%TYPE INDEX BY BINARY_INTEGER;
294 TYPE ATTRIBUTE1TAB IS TABLE OF QP_LIST_HEADERS_B.ATTRIBUTE1%TYPE INDEX BY BINARY_INTEGER;
295 TYPE ATTRIBUTE2TAB IS TABLE OF QP_LIST_HEADERS_B.ATTRIBUTE2%TYPE INDEX BY BINARY_INTEGER;
296 TYPE ATTRIBUTE3TAB IS TABLE OF QP_LIST_HEADERS_B.ATTRIBUTE3%TYPE INDEX BY BINARY_INTEGER;
297 TYPE ATTRIBUTE4TAB IS TABLE OF QP_LIST_HEADERS_B.ATTRIBUTE4%TYPE INDEX BY BINARY_INTEGER;
298 TYPE ATTRIBUTE5TAB IS TABLE OF QP_LIST_HEADERS_B.ATTRIBUTE5%TYPE INDEX BY BINARY_INTEGER;
299 TYPE ATTRIBUTE6TAB IS TABLE OF QP_LIST_HEADERS_B.ATTRIBUTE6%TYPE INDEX BY BINARY_INTEGER;
300 TYPE ATTRIBUTE7TAB IS TABLE OF QP_LIST_HEADERS_B.ATTRIBUTE7%TYPE INDEX BY BINARY_INTEGER;
301 TYPE ATTRIBUTE8TAB IS TABLE OF QP_LIST_HEADERS_B.ATTRIBUTE8%TYPE INDEX BY BINARY_INTEGER;
302 TYPE ATTRIBUTE9TAB IS TABLE OF QP_LIST_HEADERS_B.ATTRIBUTE9%TYPE INDEX BY BINARY_INTEGER;
303 TYPE ATTRIBUTE10TAB IS TABLE OF QP_LIST_HEADERS_B.ATTRIBUTE10%TYPE INDEX BY BINARY_INTEGER;
304 TYPE ATTRIBUTE11TAB IS TABLE OF QP_LIST_HEADERS_B.ATTRIBUTE11%TYPE INDEX BY BINARY_INTEGER;
305 TYPE ATTRIBUTE12TAB IS TABLE OF QP_LIST_HEADERS_B.ATTRIBUTE12%TYPE INDEX BY BINARY_INTEGER;
306 TYPE ATTRIBUTE13TAB IS TABLE OF QP_LIST_HEADERS_B.ATTRIBUTE13%TYPE INDEX BY BINARY_INTEGER;
307 TYPE ATTRIBUTE14TAB IS TABLE OF QP_LIST_HEADERS_B.ATTRIBUTE14%TYPE INDEX BY BINARY_INTEGER;
308 TYPE ATTRIBUTE15TAB IS TABLE OF QP_LIST_HEADERS_B.ATTRIBUTE15%TYPE INDEX BY BINARY_INTEGER;
309 TYPE CURRENCYTAB IS TABLE OF QP_LIST_HEADERS_B.CURRENCY_CODE%TYPE INDEX BY BINARY_INTEGER;
310 TYPE SHIP_METHOD_TAB IS TABLE OF QP_LIST_HEADERS_B.SHIP_METHOD_CODE%TYPE INDEX BY BINARY_INTEGER;
311 TYPE FREIGHT_TERMS_TAB IS TABLE OF QP_LIST_HEADERS_B.FREIGHT_TERMS_CODE%TYPE INDEX BY BINARY_INTEGER;
312 TYPE LIST_HEADER_ID_TAB IS TABLE OF QP_LIST_HEADERS_B.LIST_HEADER_ID%TYPE INDEX BY BINARY_INTEGER;
313 TYPE START_DATE_ACTIVE_TAB IS TABLE OF QP_LIST_HEADERS_B.START_DATE_ACTIVE%TYPE INDEX BY BINARY_INTEGER;
314 TYPE END_DATE_ACTIVE_TAB IS TABLE OF QP_LIST_HEADERS_B.END_DATE_ACTIVE%TYPE INDEX BY BINARY_INTEGER;
315 TYPE AUTOMATIC_FLAG_TAB IS TABLE OF QP_LIST_HEADERS_B.AUTOMATIC_FLAG%TYPE INDEX BY BINARY_INTEGER;
316 TYPE LIST_TYPE_CODE_TAB IS TABLE OF QP_LIST_HEADERS_B.LIST_TYPE_CODE%TYPE INDEX BY BINARY_INTEGER;
317 TYPE TERMS_ID_TAB IS TABLE OF QP_LIST_HEADERS_B.TERMS_ID%TYPE INDEX BY BINARY_INTEGER;
318 TYPE ROUNDING_FACTOR_TAB IS TABLE OF QP_LIST_HEADERS_B.ROUNDING_FACTOR%TYPE INDEX BY BINARY_INTEGER;
319 TYPE REQUEST_ID_TAB IS TABLE OF QP_LIST_HEADERS_B.REQUEST_ID%TYPE INDEX BY BINARY_INTEGER;
320 TYPE CREATION_DATE_TAB IS TABLE OF QP_LIST_HEADERS_B.CREATION_DATE%TYPE INDEX BY BINARY_INTEGER;
321 TYPE CREATED_BY_TAB IS TABLE OF QP_LIST_HEADERS_B.CREATED_BY%TYPE INDEX BY BINARY_INTEGER;
322 TYPE LAST_UPDATE_DATE_TAB IS TABLE OF QP_LIST_HEADERS_B.LAST_UPDATE_DATE%TYPE INDEX BY BINARY_INTEGER;
323 TYPE LAST_UPDATED_BY_TAB IS TABLE OF QP_LIST_HEADERS_B.LAST_UPDATED_BY%TYPE INDEX BY BINARY_INTEGER;
324 TYPE LAST_UPDATE_LOGIN_TAB IS TABLE OF QP_LIST_HEADERS_B.LAST_UPDATE_LOGIN%TYPE INDEX BY BINARY_INTEGER;
325 TYPE ASK_FOR_FLAG_TAB IS TABLE OF QP_LIST_HEADERS_B.ASK_FOR_FLAG%TYPE INDEX BY BINARY_INTEGER;
326 TYPE SOURCE_SYSTEM_CODE_TAB IS TABLE OF QP_LIST_HEADERS_B.SOURCE_SYSTEM_CODE%TYPE INDEX BY BINARY_INTEGER;
327 TYPE ACTIVE_FLAG_TAB IS TABLE OF QP_LIST_HEADERS_B.ACTIVE_FLAG%TYPE INDEX BY BINARY_INTEGER;
328 TYPE NAME_TAB IS TABLE OF QP_LIST_HEADERS_TL.NAME%TYPE INDEX BY BINARY_INTEGER;
329 TYPE DESCRIPTION_TAB IS TABLE OF QP_LIST_HEADERS_TL.DESCRIPTION%TYPE INDEX BY BINARY_INTEGER;
330
331 type agr_type_tab is table of VARCHAR2(3) index by binary_integer;
332
333 COMMENTS_T COMMENTSTAB;
334 CONTEXT_T CONTEXTTAB;
335 ATTRIBUTE1_T ATTRIBUTE1TAB;
336 ATTRIBUTE2_T ATTRIBUTE2TAB;
337 ATTRIBUTE3_T ATTRIBUTE3TAB;
338 ATTRIBUTE4_T ATTRIBUTE4TAB;
339 ATTRIBUTE5_T ATTRIBUTE5TAB;
340 ATTRIBUTE6_T ATTRIBUTE6TAB;
341 ATTRIBUTE7_T ATTRIBUTE7TAB;
342 ATTRIBUTE8_T ATTRIBUTE8TAB;
343 ATTRIBUTE9_T ATTRIBUTE9TAB;
344 ATTRIBUTE10_T ATTRIBUTE10TAB;
345 ATTRIBUTE11_T ATTRIBUTE11TAB;
346 ATTRIBUTE12_T ATTRIBUTE12TAB;
347 ATTRIBUTE13_T ATTRIBUTE13TAB;
348 ATTRIBUTE14_T ATTRIBUTE14TAB;
349 ATTRIBUTE15_T ATTRIBUTE15TAB;
350 CURRENCY_T CURRENCYTAB;
351 SHIP_METHOD_CODE_T SHIP_METHOD_TAB;
352 FREIGHT_TERMS_CODE_T FREIGHT_TERMS_TAB;
353 PRICE_LIST_ID_T PRICE_LIST_ID_TAB;
354 OLD_PRICE_LIST_ID_T PRICE_LIST_ID_TAB;
355 START_DATE_ACTIVE_T START_DATE_ACTIVE_TAB;
356 END_DATE_ACTIVE_T END_DATE_ACTIVE_TAB;
357 TERMS_ID_T TERMS_ID_TAB;
358 ROUNDING_FACTOR_T ROUNDING_FACTOR_TAB;
359 REQUEST_ID_T REQUEST_ID_TAB;
360 CREATION_DATE_T CREATION_DATE_TAB;
361 CREATED_BY_T CREATED_BY_TAB;
362 LAST_UPDATE_DATE_T LAST_UPDATE_DATE_TAB;
363 LAST_UPDATED_BY_T LAST_UPDATED_BY_TAB;
364 LAST_UPDATE_LOGIN_T LAST_UPDATE_LOGIN_TAB;
365 ASK_FOR_FLAG_T ASK_FOR_FLAG_TAB;
366 SOURCE_SYSTEM_CODE_T SOURCE_SYSTEM_CODE_TAB;
367 ACTIVE_FLAG_T ACTIVE_FLAG_TAB;
368 NAME_T NAME_TAB;
369 DESCRIPTION_T DESCRIPTION_TAB;
370 SEC_PRICE_LIST_ID_T PRICE_LIST_ID_TAB;
371 AGR_TYPE_T AGR_TYPE_TAB;
372 prc_list_maps prc_list_map_tbl_type;
373 new_prc_list_maps prc_list_map_tbl_type;
374 l_prc_list_map_index number := 0;
375
376 CURSOR price_list IS
377 SELECT COMMENTS,
378 CONTEXT,
379 ATTRIBUTE1,
380 ATTRIBUTE2,
381 ATTRIBUTE3,
382 ATTRIBUTE4,
383 ATTRIBUTE5,
384 ATTRIBUTE6,
385 ATTRIBUTE7,
389 ATTRIBUTE11,
386 ATTRIBUTE8,
387 ATTRIBUTE9,
388 ATTRIBUTE10,
390 ATTRIBUTE12,
391 ATTRIBUTE13,
392 ATTRIBUTE14,
393 ATTRIBUTE15,
394 CURRENCY_CODE,
395 SHIP_METHOD_CODE,
396 FREIGHT_TERMS_CODE,
397 PRICE_LIST_ID,
398 START_DATE_ACTIVE,
399 END_DATE_ACTIVE,
400 TERMS_ID,
401 ROUNDING_FACTOR,
402 REQUEST_ID,
403 CREATION_DATE,
404 CREATED_BY,
405 LAST_UPDATE_DATE,
406 LAST_UPDATED_BY,
407 LAST_UPDATE_LOGIN,
408 --NAME,
409 --DESCRIPTION,
410 PRICE_LIST_ID,
411 SECONDARY_PRICE_LIST_ID,
412 NULL
413 FROM so_price_lists_b prl
414 WHERE not exists ( select 'x'
415 from qp_discount_mapping dm
416 where dm.old_discount_id = prl.price_list_id
417 and dm.new_type in ('P','Z'))
418
419 AND not exists ( select 'x'
420 from qp_list_headers_b lh
421 where lh.list_header_id = prl.price_list_id );
422 /* vivek
423 UNION
424 SELECT COMMENTS,
425 CONTEXT,
426 ATTRIBUTE1,
427 ATTRIBUTE2,
428 ATTRIBUTE3,
429 ATTRIBUTE4,
430 ATTRIBUTE5,
431 ATTRIBUTE6,
432 ATTRIBUTE7,
433 ATTRIBUTE8,
434 ATTRIBUTE9,
435 ATTRIBUTE10,
436 ATTRIBUTE11,
437 ATTRIBUTE12,
438 ATTRIBUTE13,
439 ATTRIBUTE14,
440 ATTRIBUTE15,
441 CURRENCY_CODE,
442 SHIP_METHOD_CODE,
443 FREIGHT_TERMS_CODE,
444 PRICE_LIST_ID,
445 START_DATE_ACTIVE,
446 END_DATE_ACTIVE,
447 TERMS_ID,
448 ROUNDING_FACTOR,
449 REQUEST_ID,
450 CREATION_DATE,
451 CREATED_BY,
452 LAST_UPDATE_DATE,
453 LAST_UPDATED_BY,
454 LAST_UPDATE_LOGIN,
455 --NAME,
456 --DESCRIPTION,
457 PRICE_LIST_ID,
458 SECONDARY_PRICE_LIST_ID,
459 'AGR'
460 FROM SO_PRICE_LISTS_B PRL
461 WHERE PRL.PRICE_LIST_ID IN ( SELECT PRICE_LIST_ID
462 FROM SO_AGREEMENTS_B)
463 AND NOT EXISTS ( SELECT NULL
464 FROM QP_DISCOUNT_MAPPING
465 WHERE OLD_DISCOUNT_ID = PRL.PRICE_LIST_ID
466 AND NEW_TYPE = 'P');
467 vivek */
468 CURSOR sec_price_list IS
469 SELECT price_list_id, secondary_price_list_id
470 FROM so_price_lists_b spl
471 WHERE secondary_price_list_id is not null
472 and exists ( select list_header_id
473 from qp_LIST_headers_b
474 where list_header_id = spl.secondary_price_list_id )
475 and exists ( select list_header_id
476 from qp_LIST_headers_b
477 where list_header_id = spl.price_list_id );
478
479 cursor invalid_sec is
480 select price_list_id, secondary_price_list_id
481 from so_price_lists_b spl
482 where secondary_price_list_id is not null
483 and secondary_price_list_id not in ( select list_header_id
484 from qp_LIST_headers_b );
485
486 cursor invalid_prc is
487 select price_list_id
488 from so_price_lists_b
489 where price_list_id not in ( select list_header_id
490 from qp_LIST_headers_b );
491
492
493 BEGIN
494
495 --dbms_output.put_line('pr1');
496
497 l_header_count := 0;
498
499 OPEN price_list;
500
501 FETCH price_list BULK COLLECT INTO
502 COMMENTS_T,
503 CONTEXT_T,
504 ATTRIBUTE1_T,
505 ATTRIBUTE2_T,
506 ATTRIBUTE3_T,
507 ATTRIBUTE4_T,
508 ATTRIBUTE5_T,
509 ATTRIBUTE6_T,
510 ATTRIBUTE7_T,
511 ATTRIBUTE8_T,
512 ATTRIBUTE9_T,
513 ATTRIBUTE10_T,
514 ATTRIBUTE11_T,
515 ATTRIBUTE12_T,
516 ATTRIBUTE13_T,
517 ATTRIBUTE14_T,
518 ATTRIBUTE15_T,
519 CURRENCY_T,
520 SHIP_METHOD_CODE_T,
521 FREIGHT_TERMS_CODE_T,
522 PRICE_LIST_ID_T,
523 START_DATE_ACTIVE_T,
524 END_DATE_ACTIVE_T,
525 TERMS_ID_T,
526 ROUNDING_FACTOR_T,
527 REQUEST_ID_T,
528 CREATION_DATE_T,
529 CREATED_BY_T,
530 LAST_UPDATE_DATE_T,
531 LAST_UPDATED_BY_T,
532 LAST_UPDATE_LOGIN_T,
533 --NAME_T,
534 --DESCRIPTION_T,
535 OLD_PRICE_LIST_ID_T,
536 SEC_PRICE_LIST_ID_T,
537 AGR_TYPE_T;
538
539 CLOSE price_list;
540
541 IF price_list_id_t.FIRST is not null then
542
543 /* vivek
544 start_price_list_id := nvl(price_list_id_t.FIRST,0);
545 end_price_list_id := nvl(price_list_id_t.LAST,0);
546
547 --dbms_output.put_line('pr2');
548
549
550 FOR K in start_price_list_id..end_price_list_id loop
551
552 --dbms_output.put_line('pr2'||K);
553
554 IF ( ( AGR_TYPE_T(K) IS NOT NULL )
555 AND ( AGR_TYPE_T(K) = 'AGR' ) ) then
556
557 --dbms_output.put_line('pr2.1'||K);
558
562
559 select qp_list_headers_b_s.nextval
560 into new_price_list_id
561 from dual;
563 --dbms_output.put_line('pr2.2'||K);
564
565 --NAME_T(K) := 'QPNEW' || NAME_T(K);
566 --dbms_output.put_line('pr2.3'||K);
567
568 PRICE_LIST_ID_T(K) := new_price_list_id;
569 --dbms_output.put_line('pr2.4'||K);
570
571 prc_list_maps(PRICE_LIST_ID_T(K)).old_price_list_id := OLD_PRICE_LIST_ID_T(K);
572 --dbms_output.put_line('pr2.5'||K);
573 prc_list_maps(PRICE_LIST_ID_T(K)).new_list_header_id := PRICE_LIST_ID_T(K);
574 --dbms_output.put_line('pr2.6'||K);
575
576 prc_list_maps(PRICE_LIST_ID_T(K)).secondary_price_list_id := SEC_PRICE_LIST_ID_T(K);
577 --dbms_output.put_line('pr2.7'||K);
578 prc_list_maps(PRICE_LIST_ID_T(K)).db_flag := 'N';
579 --dbms_output.put_line('pr2.8'||K);
580
581
582 END IF;
583
584
585 END LOOP;
586
587 vivek */
588 --dbms_output.put_line('pr2.5');
589
590 l_min_price_list := 0;
591 l_max_price_list := nvl(price_list_id_t.FIRST,1) -1;
592
593 IF mod(nvl(price_list_id_t.LAST,0),p_batchsize) > 0 then
594 j := 1;
595 END IF;
596
597 numiterations := trunc(nvl(price_list_id_t.LAST,0)/p_batchsize) + j ;
598
599 WHILE ( l_max_price_list < nvl(price_list_id_t.LAST,0)
600 and numiterations > 0)
601 LOOP
602
603 l_min_price_list := l_max_price_list + 1;
604
605 IF i < numiterations then
606
607 l_max_price_list := l_min_price_list + p_batchsize -1;
608 i := i+1;
609
610 ELSE
611
612 l_max_price_list := price_list_id_t.LAST;
613
614 END IF;
615
616 BEGIN /* forall k in 1_min_price_list..l_max_price_list */
617
618 FORALL K IN l_min_price_list..l_max_price_list
619 INSERT
620 INTO qp_LIST_headers_b
621 (COMMENTS,
622 CONTEXT,
623 ATTRIBUTE1,
624 ATTRIBUTE2,
625 ATTRIBUTE3,
626 ATTRIBUTE4,
627 ATTRIBUTE5,
628 ATTRIBUTE6,
629 ATTRIBUTE7,
630 ATTRIBUTE8,
631 ATTRIBUTE9,
632 ATTRIBUTE10,
633 ATTRIBUTE11,
634 ATTRIBUTE12,
635 ATTRIBUTE13,
636 ATTRIBUTE14,
637 ATTRIBUTE15,
638 CURRENCY_CODE,
639 SHIP_METHOD_CODE,
640 FREIGHT_TERMS_CODE,
641 LIST_HEADER_ID,
642 START_DATE_ACTIVE,
643 END_DATE_ACTIVE,
644 AUTOMATIC_FLAG,
645 LIST_TYPE_CODE,
646 TERMS_ID,
647 ROUNDING_FACTOR,
648 REQUEST_ID,
649 CREATION_DATE,
650 CREATED_BY,
651 LAST_UPDATE_DATE,
652 LAST_UPDATED_BY,
653 LAST_UPDATE_LOGIN,
654 ASK_FOR_FLAG,
655 SOURCE_SYSTEM_CODE,
656 ACTIVE_FLAG)
657 VALUES (COMMENTS_T(K),
658 CONTEXT_T(K),
659 ATTRIBUTE1_T(K),
660 ATTRIBUTE2_T(K),
661 ATTRIBUTE3_T(K),
662 ATTRIBUTE4_T(K),
663 ATTRIBUTE5_T(K),
664 ATTRIBUTE6_T(K),
665 ATTRIBUTE7_T(K),
666 ATTRIBUTE8_T(K),
667 ATTRIBUTE9_T(K),
668 ATTRIBUTE10_T(K),
669 ATTRIBUTE11_T(K),
670 ATTRIBUTE12_T(K),
671 ATTRIBUTE13_T(K),
672 ATTRIBUTE14_T(K),
673 ATTRIBUTE15_T(K),
674 CURRENCY_T(K),
675 SHIP_METHOD_CODE_T(K),
676 FREIGHT_TERMS_CODE_T(K),
677 PRICE_LIST_ID_T(K),
678 START_DATE_ACTIVE_T(K),
679 END_DATE_ACTIVE_T(K),
680 'N',
681 'PRL',
682 TERMS_ID_T(K),
683 ROUNDING_FACTOR_T(K),
684 REQUEST_ID_T(K),
685 CREATION_DATE_T(K),
686 CREATED_BY_T(K),
687 LAST_UPDATE_DATE_T(K),
688 LAST_UPDATED_BY_T(K),
689 LAST_UPDATE_LOGIN_T(K),
690 'N',
691 'QP',
692 'Y');
693
694 EXCEPTION
695 WHEN OTHERS THEN
696 v_errortext := SUBSTR(SQLERRM, 1,240);
697 K := sql%rowcount + l_min_price_list;
698 ROLLBACK;
699 qp_util.log_error(price_list_id_T(K),NULL, NULL, NULL, NULL,
700 NULL, NULL,NULL, 'PRICE_LIST_HEADER_B',
701 v_errortext, 'PRICE_LISTS');
702 RAISE;
703 END;
704
705 BEGIN /* forall k in 1_min_price_list..l_max_price_list */
706
707 FORALL K IN l_min_price_list..l_max_price_list
708 insert
709 into qp_LIST_HEADERS_TL
710 (LAST_UPDATE_LOGIN,
711 NAME,
712 DESCRIPTION,
713 CREATION_DATE,
714 CREATED_BY,
715 LAST_UPDATE_DATE,
716 LAST_UPDATED_BY,
717 LIST_HEADER_ID,
718 LANGUAGE,
719 SOURCE_LANG,
720 VERSION_NO)
721 select LAST_UPDATE_LOGIN_T(K),
722 ptl.NAME,
723 ptl.description,
727 LAST_UPDATED_BY_T(K),
724 CREATION_DATE_T(K),
725 CREATED_BY_T(K),
726 LAST_UPDATE_DATE_T(K),
728 PRICE_LIST_ID_T(K),
729 L.LANGUAGE_CODE,
730 userenv('LANG'),
731 '1'
732 from so_price_lists_tl ptl,
733 FND_LANGUAGES L
734 where ptl.price_list_id = price_list_id_T(K)
735 and ptl.language = l.language_code
736 and L.INSTALLED_FLAG in ('I', 'B')
737 and NOT EXISTS (
738 SELECT NULL
739 FROM qp_list_headers_tl ptl
740 WHERE ptl.list_header_id = price_list_id_T(K)
741 AND ptl.language = l.language_code);
742
743 EXCEPTION
744 WHEN OTHERS THEN
745 v_errortext := SUBSTR(SQLERRM, 1,240);
746 K := sql%rowcount + l_min_price_list;
747 -- dbms_output.put_line('The value of K: ' || K);
748 ROLLBACK;
749 qp_util.log_error(price_list_id_T(K),
750 NULL, NULL, NULL, NULL, NULL,
751 NULL, NULL, 'PRICE_LIST_HEADER_TL',
752 v_errortext, 'PRICE_LISTS');
753 RAISE;
754 END;
755
756 /* insert into qp_discount_mapping table for the rows that were
757 just now inserted */
758
759 --dbms_output.put_line('pr2.6');
760
761 new_prc_list_maps := prc_list_maps;
762
763 l_prc_list_map_index := new_prc_list_maps.FIRST;
764
765 while l_prc_list_map_index is not null loop
766
767 IF new_prc_list_maps(l_prc_list_map_index).db_flag = 'N' THEN
768
769 begin
770
771 INSERT INTO QP_DISCOUNT_MAPPING(OLD_DISCOUNT_ID,
772 OLD_DISCOUNT_LINE_ID,
773 NEW_LIST_HEADER_ID,
774 NEW_LIST_LINE_ID,
775 OLD_PRICE_BREAK_LINES_LOW,
776 OLD_PRICE_BREAK_LINES_HIGH,
777 OLD_METHOD_TYPE_CODE,
778 OLD_PRICE_BREAK_PERCENT,
779 OLD_PRICE_BREAK_AMOUNT,
780 OLD_PRICE_BREAK_PRICE,
781 NEW_TYPE,
782 PRICING_CONTEXT)
783 VALUES (new_prc_list_maps(l_prc_list_map_index).old_price_list_id,
784 NULL,
785 new_prc_list_maps(l_prc_list_map_index).new_list_header_id,
786 NULL,
787 NULL,
788 NULL,
789 NULL,
790 NULL,
791 NULL,
792 NULL,
793 'P',
794 NULL);
795
796 new_prc_list_maps(l_prc_list_map_index).db_flag := 'Y';
797
798 exception
799
800 when others then
801
802 v_errortext := SUBSTR(SQLERRM, 1,240);
803 --dbms_output.put_line('hello 1');
804 new_prc_list_maps := prc_list_maps;
805 qp_util.log_error(NULL, NULL, NULL, NULL, NULL, NULL, NULL,
806 NULL, 'disc_map', v_errortext, 'PRICE_LISTS');
807 RAISE;
808
809 end;
810
811 END IF;
812
813 l_prc_list_map_index := new_prc_list_maps.NEXT(l_prc_list_map_index);
814
815 end loop; /* while l_prc_list_map_index is not null loop */
816
817 --dbms_output.put_line('pr2.7');
818
819 prc_list_maps := new_prc_list_maps;
820
821
822 COMMIT; /* commiting after finishing some multiple of p_batchsize price
823 lists. */
824
825 --dbms_output.put_line('pr2.8');
826
827
828
829 END LOOP; /* while loop */
830
831
832 END IF; /* if price_list_id_t.first is not null */
833
834
835 --create_list_lines(p_batchsize);
836
837 -- COMMIT;
838
839
840
841 /*
842
843 EXCEPTION
844
845 WHEN OTHERS THEN
846 v_errortext := SUBSTR(SQLERRM, 1,240);
847 ROLLBACK;
848 qp_util.log_error(NULL, NULL, NULL, NULL, NULL, NULL, NULL,
849 NULL, 'OTHERS', v_errortext, 'PRICE_LISTS');
850 RAISE;
851 END;
852
853 */
854
855
856 --END LOOP;
857
858 /* creating qualifiers for secondary price list */
859
860 /* hard code the l_context, and l_attribute */
861
862 --dbms_output.put_line('pr3');
863 l_context := 'MODLIST';
864 l_attribute := 'QUALIFIER_ATTRIBUTE4';
865
866 OPEN sec_price_list;
867 FETCH sec_price_list into l_price_list_id, l_secondary_price_list_id;
868
869 IF sec_price_list%NOTFOUND THEN
870
871 null;
872
873 else
874
875 BEGIN
876
877 /* Renga */
878
879
880 --dbms_output.put_line('pr4');
881
882 QP_UTIL.Get_Qual_Flex_Properties(l_context,
883 l_attribute,
884 l_price_list_id,
885 l_datatype,
886 l_precedence,
887 l_error_code);
888
889 IF l_error_code <> 0 THEN
890 RAISE e_validate_flexfield;
891 END IF;
892 EXCEPTION
893 WHEN e_validate_flexfield THEN
894 err_num := SQLCODE;
895 err_msg := SUBSTR(SQLERRM, 1, 240);
896 qp_util.log_error(l_price_list_id, l_secondary_price_list_id, NULL, NULL, NULL, NULL, NULL, NULL, 'GET_QUAL_FLEX_LISTS', err_msg, 'PRICE_LISTS');
897
898 WHEN OTHERS THEN
899 err_num := SQLCODE;
900 err_msg := SUBSTR(SQLERRM, 1, 240);
901 ROLLBACK;
902 qp_util.log_error(l_price_list_id, l_secondary_price_list_id, NULL, NULL, NULL, NULL, NULL, NULL, 'GET_QUAL_FLEX_LISTS', err_msg, 'PRICE_LISTS');
903 RAISE;
904 END;
905
906 end if;
907
908
909 --dbms_output.put_line('pr5');
910
911 LOOP
912
913 /* create qualifier with price list as the qualifier and list
914 header id as secondary_price_list_id */
915
916 /* Commenting the qualifier_grouping_no = -2 since this resulted in creating duplicate price list qualifiers for the same secondary price list. Honeywell Bug no. 1781220 */
917
918 EXIT WHEN sec_price_list%NOTFOUND;
919
920 BEGIN
921 --dbms_output.put_line('pr6');
922
923 INSERT INTO qp_QUALIFIERS (
924 QUALIFIER_ID,
925 CREATION_DATE,
926 CREATED_BY,
927 LAST_UPDATE_DATE,
928 LAST_UPDATED_BY,
929 LAST_UPDATE_LOGIN,
930 LIST_HEADER_ID,
931 COMPARISON_OPERATOR_CODE,
932 QUALIFIER_CONTEXT,
933 QUALIFIER_ATTRIBUTE,
934 QUALIFIER_ATTR_VALUE,
935 QUALIFIER_GROUPING_NO,
936 EXCLUDER_FLAG,
937 QUALIFIER_DATATYPE,
938 QUALIFIER_PRECEDENCE,
939 QUALIFIER_ATTR_VALUE_TO
940 --ENH Upgrade BOAPI for orig_sys...ref RAVI
941 ,ORIG_SYS_QUALIFIER_REF
942 ,ORIG_SYS_LINE_REF
943 ,ORIG_SYS_HEADER_REF
944 )
945 select
946 QP_QUALIFIERS_S.nextval,
947 sysdate,
948 1,
949 sysdate,
950 1,
951 1,
952 l_secondary_price_list_id,
953 '=',
954 l_context,
955 l_attribute,
956 l_price_list_id,
957 -1, -- for bug 2021623 qp_qualifier_group_no_s.nextval,
958 'N',
959 l_datatype,
960 l_precedence,
961 NULL
962 --ENH Upgrade BOAPI for orig_sys...ref RAVI
963 ,to_char(QP_QUALIFIERS_S.currval)
964 ,null
965 ,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=l_secondary_price_list_id)
966 from dual
967 WHERE NOT EXISTS ( select null
968 from qp_qualifiers
969 where qualifier_context = 'MODLIST'
970 and qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'
971 and qualifier_attr_value = to_char(l_price_list_id)
972 and comparison_operator_code = '='
973 -- and qualifier_grouping_no = -2
974 and list_header_id = l_secondary_price_list_id
975 and qualifier_rule_id is null
976 and qualifier_attr_value_to is null );
977
978 EXCEPTION
979 WHEN OTHERS THEN
980 v_errortext := SUBSTR(SQLERRM, 1,240);
981 ROLLBACK;
982 qp_util.log_error(l_price_list_id,
983 NULL, l_secondary_price_list_id, NULL, NULL, NULL, NULL,
984 NULL, 'SECONDARY_PRICE_LISTS',
985 v_errortext, 'PRICE_LISTS');
986 RAISE;
987 END;
988
989
990 /* vivek
991
992 --dbms_output.put_line('pr7');
993 IF prc_list_maps.EXISTS(l_price_list_id) THEN
994
995 BEGIN
996
997 --dbms_output.put_line('pr8');
998 INSERT INTO qp_QUALIFIERS (
999 QUALIFIER_ID,
1000 CREATION_DATE,
1001 CREATED_BY,
1002 LAST_UPDATE_DATE,
1003 LAST_UPDATED_BY,
1004 LAST_UPDATE_LOGIN,
1005 LIST_HEADER_ID,
1006 COMPARISON_OPERATOR_CODE,
1007 QUALIFIER_CONTEXT,
1008 QUALIFIER_ATTRIBUTE,
1009 QUALIFIER_ATTR_VALUE,
1010 QUALIFIER_GROUPING_NO,
1011 EXCLUDER_FLAG,
1012 QUALIFIER_DATATYPE,
1013 QUALIFIER_PRECEDENCE,
1014 QUALIFIER_ATTR_VALUE_TO)
1015 select
1016 QP_QUALIFIERS_S.nextval,
1017 sysdate,
1018 1,
1019 sysdate,
1020 1,
1021 1,
1025 l_attribute,
1022 prc_list_maps(l_price_list_id).secondary_price_list_id,
1023 '=',
1024 l_context,
1026 prc_list_maps(l_price_list_id).new_list_header_id,
1027 qp_qualifier_group_no_s.nextval,
1028 'N',
1029 l_datatype,
1030 l_precedence,
1031 NULL
1032 from dual
1033 WHERE NOT EXISTS ( select null
1034 from qp_qualifiers
1035 where qualifier_context = 'MODLIST'
1036 and qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'
1037 and qualifier_attr_value = prc_list_maps(l_price_list_id).new_list_header_id
1038 and comparison_operator_code = '='
1039 and qualifier_grouping_no = -2
1040 and list_header_id = prc_list_maps(l_price_list_id).secondary_price_list_id
1041 and qualifier_rule_id is null
1042 and qualifier_attr_value_to is null );
1043 --dbms_output.put_line('pr9');
1044
1045 EXCEPTION
1046 WHEN OTHERS THEN
1047 v_errortext := SUBSTR(SQLERRM, 1,240);
1048 ROLLBACK;
1049 qp_util.log_error(prc_list_maps(l_price_list_id).new_list_header_id,
1050 NULL, l_secondary_price_list_id, NULL,
1051 NULL, NULL, NULL,
1052 NULL, 'SECONDARY_PRICE_LISTS',
1053 v_errortext, 'PRICE_LISTS');
1054 RAISE;
1055
1056 END;
1057
1058 END IF;
1059
1060 vivek */
1061
1062 FETCH sec_price_list into l_price_list_id, l_secondary_price_list_id;
1063
1064 END LOOP;
1065
1066 CLOSE sec_price_list;
1067
1068 commit;
1069
1070
1071 -- Price Lists not migrated
1072 --dbms_output.put_line('pr10');
1073
1074 FOR invalid_prc_rec in invalid_prc
1075 LOOP
1076 QP_Util.Log_Error(p_id1 => invalid_prc_rec.price_list_id,
1077 p_id2 => NULL,
1078 p_error_type => 'PRICE_LIST_NOT_MIGRATED',
1079 p_error_desc => 'Price List Id ' || invalid_prc_rec.price_list_id || ' was not migrated. Please check qp_upgrade_errors for more details.',
1080 p_error_module => 'PRICE_LISTS');
1081 END LOOP;
1082
1083 --dbms_output.put_line('pr11');
1084 /* price lists for which secondary price lists did not get created */
1085 -- Secondary price lists Not Migrated
1086
1087 FOR invalid_sec_rec in invalid_sec
1088 LOOP
1089 QP_Util.Log_Error(p_id1 => invalid_sec_rec.secondary_price_list_id,
1090 p_id2 => invalid_sec_rec.price_list_id,
1091 p_error_type => 'SECONDARY_LISTS_NOT_MIGRATED',
1092 p_error_desc => 'Price List Id ' || invalid_sec_rec.secondary_price_list_id || ' does not exist. Hence Price List Id ' || invalid_sec_rec.price_list_id ||'is not created as a qualifier',
1093 p_error_module => 'PRICE_LISTS');
1094
1095 END LOOP;
1096 --dbms_output.put_line('pr12');
1097
1098 -- Upgrade_Flex_Structures;
1099
1100 -- QP_UTIL.QP_UPGRADE_CONTEXT('OE', 'QP', 'SO_PRICE_LISTS', 'QP_LIST_HEADERS');
1101 -- QP_UTIL.QP_UPGRADE_CONTEXT('OE', 'QP', 'SO_PRICE_LIST_LINES', 'QP_LIST_LINES');
1102
1103 EXCEPTION
1104
1105 WHEN OTHERS THEN
1106 v_errortext := SUBSTR(SQLERRM, 1,240);
1107 ROLLBACK;
1108 qp_util.log_error(NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1109 NULL, 'MAIN', v_errortext, 'PRICE_LISTS');
1110
1111 /*
1112
1113 -- Price Lists not migrated
1114
1115 FOR invalid_prc_rec in invalid_prc
1116 LOOP
1117 QP_Util.Log_Error(p_id1 => invalid_prc_rec.price_list_id,
1118 p_id2 => NULL,
1119 p_error_type => 'PRICE_LIST_NOT_MIGRATED',
1120 p_error_desc => 'Price List Id ' || invalid_prc_rec.price_list_id || ' was not migrated. Please check qp_upgrade_errors for more details.',
1121 p_error_module => 'PRICE_LISTS');
1122 END LOOP;
1123 -- Secondary price lists Not Migrated
1124
1125 FOR invalid_sec_rec in invalid_sec
1126 LOOP
1127 QP_Util.Log_Error(p_id1 => invalid_sec_rec.secondary_price_list_id,
1128 p_id2 => invalid_sec_rec.price_list_id,
1129 p_error_type => 'SECONDARY_LISTS_NOT_MIGRATED',
1130 p_error_desc => 'Price List Id ' || invalid_sec_rec.secondary_price_list_id || ' does not exist. Hence Price List Id ' || invalid_sec_rec.price_list_id ||'is not created as a qualifier',
1131 p_error_module => 'PRICE_LISTS');
1132 END LOOP;
1133 */
1134
1135
1136 RAISE;
1137
1138 end create_price_list;
1139
1140
1141 PROCEDURE create_list_lines(p_batchsize IN NUMBER := 5000,
1142 l_worker in number := 1)
1143 IS
1144 l_product_context varchar2(30);
1145 l_customer_item_context varchar2(30);
1146 l_product_attr varchar2(30);
1147 l_customer_item_attr varchar2(30);
1148 l_pricing_attr_rec pricing_attr_rec_type;
1149 l_pricing_attr_tbl pricing_attr_tbl_type;
1150 l_list_line_id number;
1151 l_attribute_grouping_no number;
1152 l_pricing_attribute_id number;
1153 err_num NUMBER;
1154 err_msg VARCHAR2(100);
1155 l_prod_datatype VARCHAR2(30);
1156 l_prod_precedence NUMBER;
1157 l_error NUMBER;
1158 l_error1 NUMBER;
1159 l_prc_datatype VARCHAR2(30);
1160 l_prc_precedence NUMBER;
1161 v_errortext VARCHAR2(240);
1162 l_primary_uom_code VARCHAR2(3);
1166 l_max_line number := p_batchsize;
1163 l_primary_uom_flag VARCHAR2(1) := 'N';
1164 l_line_count number := 0;
1165 l_min_line number := 1;
1167 numiterations number := 1;
1168 i number := 1;
1169 j number := 0;
1170 K number := 0;
1171 v_min_line number := 0;
1172 v_max_line number := 0;
1173 attr_count number := 0;
1174 agr_count number := 0;
1175 new_prc_list_line_id number := 0;
1176 start_prc_list_line_id number := 0;
1177 end_prc_list_line_id number := 0;
1178
1179 e_get_prod_flex_properties EXCEPTION;
1180
1181 TYPE price_list_line_id_tab is table of
1182 SO_PRICE_LIST_LINES_115.price_list_line_id%TYPE INDEX BY BINARY_INTEGER;
1183 TYPE creation_date_tab is table of SO_PRICE_LIST_LINES_115.creation_date%TYPE INDEX BY BINARY_INTEGER;
1184 TYPE created_by_tab is table of SO_PRICE_LIST_LINES_115.created_by%TYPE INDEX BY BINARY_INTEGER;
1185 TYPE last_update_date_tab is table of
1186 SO_PRICE_LIST_LINES_115.last_update_date%TYPE INDEX BY BINARY_INTEGER;
1187 TYPE last_updated_by_tab is table of
1188 SO_PRICE_LIST_LINES_115.last_updated_by%TYPE INDEX BY BINARY_INTEGER;
1189 TYPE last_update_login_tab is table of
1190 SO_PRICE_LIST_LINES_115.last_update_login%TYPE INDEX BY BINARY_INTEGER;
1191 TYPE program_application_id_tab is table of
1192 SO_PRICE_LIST_LINES_115.program_application_id%TYPE INDEX BY BINARY_INTEGER;
1193 TYPE program_id_tab is table of SO_PRICE_LIST_LINES_115.program_id%TYPE INDEX BY BINARY_INTEGER;
1194 TYPE program_update_date_tab is table of
1195 SO_PRICE_LIST_LINES_115.program_update_date%TYPE INDEX BY BINARY_INTEGER;
1196 TYPE request_id_tab is table of SO_PRICE_LIST_LINES_115.request_id%TYPE INDEX BY BINARY_INTEGER;
1197 TYPE price_list_id_tab is table of SO_PRICE_LIST_LINES_115.price_list_id%TYPE INDEX BY BINARY_INTEGER;
1198 TYPE inventory_item_id_tab is table of
1199 SO_PRICE_LIST_LINES_115.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
1200 TYPE unit_code_tab is table of SO_PRICE_LIST_LINES_115.unit_code%TYPE INDEX BY BINARY_INTEGER;
1201 TYPE method_code_tab is table of SO_PRICE_LIST_LINES_115.method_code%TYPE INDEX BY BINARY_INTEGER;
1202 TYPE list_price_tab is table of SO_PRICE_LIST_LINES_115.list_price%TYPE INDEX BY BINARY_INTEGER;
1203 TYPE pricing_rule_id_tab is table of
1204 SO_PRICE_LIST_LINES_115.pricing_rule_id%TYPE INDEX BY BINARY_INTEGER;
1205 TYPE reprice_flag_tab is table of SO_PRICE_LIST_LINES_115.reprice_flag%TYPE INDEX BY BINARY_INTEGER;
1206 TYPE pricing_context_tab is table of
1207 SO_PRICE_LIST_LINES_115.pricing_context%TYPE INDEX BY BINARY_INTEGER;
1208 TYPE pricing_attribute1_tab is table of
1209 SO_PRICE_LIST_LINES_115.pricing_attribute1%TYPE INDEX BY BINARY_INTEGER;
1210 TYPE pricing_attribute2_tab is table of
1211 SO_PRICE_LIST_LINES_115.pricing_attribute2%TYPE INDEX BY BINARY_INTEGER;
1212 TYPE pricing_attribute3_tab is table of
1213 SO_PRICE_LIST_LINES_115.pricing_attribute3%TYPE INDEX BY BINARY_INTEGER;
1214 TYPE pricing_attribute4_tab is table of
1215 SO_PRICE_LIST_LINES_115.pricing_attribute4%TYPE INDEX BY BINARY_INTEGER;
1216 TYPE pricing_attribute5_tab is table of
1217 SO_PRICE_LIST_LINES_115.pricing_attribute5%TYPE INDEX BY BINARY_INTEGER;
1218 TYPE pricing_attribute6_tab is table of
1219 SO_PRICE_LIST_LINES_115.pricing_attribute6%TYPE INDEX BY BINARY_INTEGER;
1220 TYPE pricing_attribute7_tab is table of
1221 SO_PRICE_LIST_LINES_115.pricing_attribute7%TYPE INDEX BY BINARY_INTEGER;
1222 TYPE pricing_attribute8_tab is table of
1223 SO_PRICE_LIST_LINES_115.pricing_attribute8%TYPE INDEX BY BINARY_INTEGER;
1224 TYPE pricing_attribute9_tab is table of
1225 SO_PRICE_LIST_LINES_115.pricing_attribute9%TYPE INDEX BY BINARY_INTEGER;
1226 TYPE pricing_attribute10_tab is table of
1227 SO_PRICE_LIST_LINES_115.pricing_attribute10%TYPE INDEX BY BINARY_INTEGER;
1228 TYPE pricing_attribute11_tab is table of
1229 SO_PRICE_LIST_LINES_115.pricing_attribute11%TYPE INDEX BY BINARY_INTEGER;
1230 TYPE pricing_attribute12_tab is table of
1231 SO_PRICE_LIST_LINES_115.pricing_attribute12%TYPE INDEX BY BINARY_INTEGER;
1232 TYPE pricing_attribute13_tab is table of
1233 SO_PRICE_LIST_LINES_115.pricing_attribute13%TYPE INDEX BY BINARY_INTEGER;
1234 TYPE pricing_attribute14_tab is table of
1235 SO_PRICE_LIST_LINES_115.pricing_attribute14%TYPE INDEX BY BINARY_INTEGER;
1236 TYPE pricing_attribute15_tab is table of
1237 SO_PRICE_LIST_LINES_115.pricing_attribute15%TYPE INDEX BY BINARY_INTEGER;
1238 TYPE start_date_active_tab is table of
1239 SO_PRICE_LIST_LINES_115.start_date_active%TYPE INDEX BY BINARY_INTEGER;
1240 TYPE end_date_active_tab is table of
1241 SO_PRICE_LIST_LINES_115.end_date_active%TYPE INDEX BY BINARY_INTEGER;
1242 TYPE context_tab is table of SO_PRICE_LIST_LINES_115.context%TYPE INDEX BY BINARY_INTEGER;
1243 TYPE attribute1_tab is table of SO_PRICE_LIST_LINES_115.attribute1%TYPE INDEX BY BINARY_INTEGER;
1244 TYPE attribute2_tab is table of SO_PRICE_LIST_LINES_115.attribute2%TYPE INDEX BY BINARY_INTEGER;
1245 TYPE attribute3_tab is table of SO_PRICE_LIST_LINES_115.attribute3%TYPE INDEX BY BINARY_INTEGER;
1246 TYPE attribute4_tab is table of SO_PRICE_LIST_LINES_115.attribute4%TYPE INDEX BY BINARY_INTEGER;
1247 TYPE attribute5_tab is table of SO_PRICE_LIST_LINES_115.attribute5%TYPE INDEX BY BINARY_INTEGER;
1248 TYPE attribute6_tab is table of SO_PRICE_LIST_LINES_115.attribute6%TYPE INDEX BY BINARY_INTEGER;
1249 TYPE attribute7_tab is table of SO_PRICE_LIST_LINES_115.attribute7%TYPE INDEX BY BINARY_INTEGER;
1250 TYPE attribute8_tab is table of SO_PRICE_LIST_LINES_115.attribute8%TYPE INDEX BY BINARY_INTEGER;
1251 TYPE attribute9_tab is table of SO_PRICE_LIST_LINES_115.attribute9%TYPE INDEX BY BINARY_INTEGER;
1252 TYPE attribute10_tab is table of SO_PRICE_LIST_LINES_115.attribute10%TYPE INDEX BY BINARY_INTEGER;
1256 TYPE attribute14_tab is table of SO_PRICE_LIST_LINES_115.attribute14%TYPE INDEX BY BINARY_INTEGER;
1253 TYPE attribute11_tab is table of SO_PRICE_LIST_LINES_115.attribute11%TYPE INDEX BY BINARY_INTEGER;
1254 TYPE attribute12_tab is table of SO_PRICE_LIST_LINES_115. attribute12%TYPE INDEX BY BINARY_INTEGER;
1255 TYPE attribute13_tab is table of SO_PRICE_LIST_LINES_115.attribute13%TYPE INDEX BY BINARY_INTEGER;
1257 TYPE attribute15_tab is table of SO_PRICE_LIST_LINES_115.attribute15%TYPE INDEX BY BINARY_INTEGER;
1258
1259 /* pricing attribute specific tab types */
1260
1261
1262
1263 type attr_creation_date_tab is table of QP_PRICING_ATTRIBUTES.CREATION_DATE%TYPE INDEX BY BINARY_INTEGER;
1264 type attr_created_by_tab is table of QP_PRICING_ATTRIBUTES.CREATED_BY%TYPE INDEX BY BINARY_INTEGER;
1265 type attr_last_update_date_tab is table of QP_PRICING_ATTRIBUTES.LAST_UPDATE_DATE%TYPE INDEX BY BINARY_INTEGER;
1266 type attr_last_updated_by_tab is table of QP_PRICING_ATTRIBUTES.LAST_UPDATED_BY%TYPE INDEX BY BINARY_INTEGER;
1267 type attr_last_update_login_tab is table of QP_PRICING_ATTRIBUTES.LAST_UPDATE_LOGIN%TYPE INDEX BY BINARY_INTEGER;
1268 type attr_program_appl_id_tab is table of QP_PRICING_ATTRIBUTES.PROGRAM_APPLICATION_ID%TYPE INDEX BY BINARY_INTEGER;
1269 type attr_program_id_tab is table of QP_PRICING_ATTRIBUTES.PROGRAM_ID%TYPE INDEX BY BINARY_INTEGER;
1270 type attr_program_update_date_tab is table of QP_PRICING_ATTRIBUTES.PROGRAM_UPDATE_DATE%TYPE INDEX BY BINARY_INTEGER;
1271 type attr_request_id_tab is table of QP_PRICING_ATTRIBUTES.REQUEST_ID%TYPE INDEX BY BINARY_INTEGER;
1272 type attr_list_line_id_tab is table of QP_PRICING_ATTRIBUTES.LIST_LINE_ID%TYPE INDEX BY BINARY_INTEGER;
1273 type attr_excluder_flag_tab is table of QP_PRICING_ATTRIBUTES.EXCLUDER_FLAG%TYPE INDEX BY BINARY_INTEGER;
1274 type attr_accumulate_flag_tab is table of QP_PRICING_ATTRIBUTES.ACCUMULATE_FLAG%TYPE INDEX BY BINARY_INTEGER;
1275 type attr_product_attr_context_tab is table of QP_PRICING_ATTRIBUTES.PRODUCT_ATTRIBUTE_CONTEXT%TYPE INDEX BY BINARY_INTEGER;
1276 type attr_product_attribute_tab is table of QP_PRICING_ATTRIBUTES.PRODUCT_ATTRIBUTE%TYPE INDEX BY BINARY_INTEGER;
1277 type attr_product_attr_value_tab is table of QP_PRICING_ATTRIBUTES.PRODUCT_ATTR_VALUE%TYPE INDEX BY BINARY_INTEGER;
1278 type attr_product_uom_code_tab is table of QP_PRICING_ATTRIBUTES.PRODUCT_UOM_CODE%TYPE INDEX BY BINARY_INTEGER;
1279 type attr_comparison_operator_tab is table of QP_PRICING_ATTRIBUTES.COMPARISON_OPERATOR_CODE%TYPE INDEX BY BINARY_INTEGER;
1280 type attr_pricing_context_tab is table of QP_PRICING_ATTRIBUTES.PRICING_ATTRIBUTE_CONTEXT%TYPE INDEX BY BINARY_INTEGER;
1281 type attr_pricing_attr_tab is table of QP_PRICING_ATTRIBUTES.PRICING_ATTRIBUTE%TYPE INDEX BY BINARY_INTEGER;
1282 type attr_pricing_attr_val_from_tab is table of QP_PRICING_ATTRIBUTES.PRICING_ATTR_VALUE_FROM%TYPE INDEX BY BINARY_INTEGER;
1283 type attr_pricing_attr_val_to_tab is table of QP_PRICING_ATTRIBUTES.PRICING_ATTR_VALUE_TO%TYPE INDEX BY BINARY_INTEGER;
1284 type attr_prc_datatype_tab is table of QP_PRICING_ATTRIBUTES.PRICING_ATTRIBUTE_DATATYPE%TYPE INDEX BY BINARY_INTEGER;
1285 type agr_prc_list_tab is table of NUMBER index by binary_integer;
1286 type agr_list_header_tab is table of NUMBER index by binary_integer;
1287 type agr_type_tab is table of VARCHAR2(3) index by binary_integer;
1288 TYPE qualification_ind_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1289 TYPE sec_prc_list_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1290
1291 price_list_line_ids price_list_line_id_tab;
1292 creation_dates creation_date_tab;
1293 created_bys created_by_tab;
1294 last_update_dates last_update_date_tab;
1295 last_updated_bys last_updated_by_tab;
1296 last_update_logins last_update_login_tab;
1297 program_application_ids program_application_id_tab;
1298 program_ids program_id_tab;
1299 program_update_dates program_update_date_tab;
1300 request_ids request_id_tab;
1301 price_list_ids price_list_id_tab;
1302 inventory_item_ids inventory_item_id_tab;
1303 unit_codes unit_code_tab;
1304 method_codes method_code_tab;
1305 list_prices list_price_tab;
1306 pricing_rule_ids pricing_rule_id_tab;
1307 reprice_flags reprice_flag_tab;
1308 pricing_contexts pricing_context_tab;
1309 pricing_attribute1s pricing_attribute1_tab;
1310 pricing_attribute2s pricing_attribute2_tab;
1311 pricing_attribute3s pricing_attribute3_tab;
1312 pricing_attribute4s pricing_attribute4_tab;
1313 pricing_attribute5s pricing_attribute5_tab;
1314 pricing_attribute6s pricing_attribute6_tab;
1315 pricing_attribute7s pricing_attribute7_tab;
1316 pricing_attribute8s pricing_attribute8_tab;
1317 pricing_attribute9s pricing_attribute9_tab;
1318 pricing_attribute10s pricing_attribute10_tab;
1319 pricing_attribute11s pricing_attribute11_tab;
1320 pricing_attribute12s pricing_attribute12_tab;
1321 pricing_attribute13s pricing_attribute13_tab;
1322 pricing_attribute14s pricing_attribute14_tab;
1323 pricing_attribute15s pricing_attribute15_tab;
1324 start_date_actives start_date_active_tab;
1325 end_date_actives end_date_active_tab;
1326 contexts context_tab;
1327 attribute1s attribute1_tab;
1328 attribute2s attribute2_tab;
1329 attribute3s attribute3_tab;
1330 attribute4s attribute4_tab;
1331 attribute5s attribute5_tab;
1332 attribute6s attribute6_tab;
1333 attribute7s attribute7_tab;
1334 attribute8s attribute8_tab;
1335 attribute9s attribute9_tab;
1336 attribute10s attribute10_tab;
1337 attribute11s attribute11_tab;
1338 attribute12s attribute12_tab;
1339 attribute13s attribute13_tab;
1340 attribute14s attribute14_tab;
1341 attribute15s attribute15_tab;
1342
1343 /* pricing attribute related datatypes */
1344
1345 attr_creation_dates attr_creation_date_tab;
1346 attr_created_bys attr_created_by_tab;
1347 attr_last_update_dates attr_last_update_date_tab;
1351 attr_program_ids attr_program_id_tab;
1348 attr_last_updated_bys attr_last_updated_by_tab;
1349 attr_last_update_logins attr_last_update_login_tab;
1350 attr_program_application_ids attr_program_appl_id_tab;
1352 attr_program_update_dates attr_program_update_date_tab;
1353 attr_request_ids attr_request_id_tab;
1354 attr_list_line_ids attr_list_line_id_tab;
1355 attr_excluder_flags attr_excluder_flag_tab;
1356 attr_accumulate_flags attr_accumulate_flag_tab;
1357 attr_product_attr_contexts attr_product_attr_context_tab;
1358 attr_product_attributes attr_product_attribute_tab;
1359 attr_product_attr_values attr_product_attr_value_tab;
1360 attr_product_uom_codes attr_product_uom_code_tab;
1361 attr_comparison_operator_codes attr_comparison_operator_tab;
1362 attr_pricing_contexts attr_pricing_context_tab;
1363 attr_pricing_attrs attr_pricing_attr_tab;
1364 attr_pricing_attr_value_froms attr_pricing_attr_val_from_tab;
1365 attr_pricing_attr_value_tos attr_pricing_attr_val_to_tab;
1366 prc_datatypes attr_prc_datatype_tab;
1367 agr_old_price_lists agr_prc_list_tab;
1368 agr_new_list_headers agr_list_header_tab;
1369 agr_types agr_type_tab;
1370 old_price_list_line_ids price_list_line_id_tab;
1371 qualification_ind qualification_ind_tab;
1372 sec_prc_list_ids sec_prc_list_tab;
1373
1374 prc_list_maps prc_list_map_tbl_type;
1375 new_prc_list_maps prc_list_map_tbl_type;
1376 l_prc_list_map_index number := 0;
1377 v_segs_upg_t qp_util.v_segs_upg_tab;
1378 l_pricing_context varchar2(30) := NULL;
1379
1380 cursor prc_list_line IS
1381 SELECT
1382 price_list_line_id,
1383 creation_date,
1384 created_by,
1385 last_update_date,
1386 last_updated_by,
1387 last_update_login,
1388 program_application_id,
1389 program_id,
1390 program_update_date,
1391 request_id,
1392 price_list_id,
1393 inventory_item_id,
1394 unit_code,
1395 method_code,
1396 list_price,
1397 pricing_rule_id,
1398 reprice_flag,
1399 pricing_context,
1400 pricing_attribute1,
1401 pricing_attribute2,
1402 pricing_attribute3,
1403 pricing_attribute4,
1404 pricing_attribute5,
1405 pricing_attribute6,
1406 pricing_attribute7,
1407 pricing_attribute8,
1408 pricing_attribute9,
1409 pricing_attribute10,
1410 pricing_attribute11,
1411 pricing_attribute12,
1412 pricing_attribute13,
1413 pricing_attribute14,
1414 pricing_attribute15,
1415 start_date_active,
1416 end_date_active,
1417 context,
1418 attribute1,
1419 attribute2,
1420 attribute3,
1421 attribute4,
1422 attribute5,
1423 attribute6,
1424 attribute7,
1425 attribute8,
1426 attribute9,
1427 attribute10,
1428 attribute11,
1429 attribute12,
1430 attribute13,
1431 attribute14,
1432 attribute15,
1433 price_list_line_id,
1434 NULL,
1435 3
1436 from so_price_list_lines_115 prl
1437 where prl.price_list_id in ( select list_header_id
1438 from qp_LIST_headers_b
1439 where list_type_code = 'PRL')
1440 and not exists ( select null
1441 from qp_LIST_lines
1442 where list_line_id = prl.price_list_line_id )
1443 and prl.price_list_line_id between l_min_line and l_max_line;
1444
1445 /* vivek
1446
1447 union
1448 SELECT
1449 prl.price_list_line_id,
1450 prl.creation_date,
1451 prl.created_by,
1452 prl.last_update_date,
1453 prl.last_updated_by,
1454 prl.last_update_login,
1455 prl.program_application_id,
1456 prl.program_id,
1457 prl.program_update_date,
1458 prl.request_id,
1459 prl.price_list_id,
1460 prl.inventory_item_id,
1461 prl.unit_code,
1462 prl.method_code,
1463 prl.list_price,
1464 prl.pricing_rule_id,
1465 prl.reprice_flag,
1466 prl.pricing_context,
1467 prl.pricing_attribute1,
1468 prl.pricing_attribute2,
1469 prl.pricing_attribute3,
1470 prl.pricing_attribute4,
1471 prl.pricing_attribute5,
1472 prl.pricing_attribute6,
1473 prl.pricing_attribute7,
1474 prl.pricing_attribute8,
1475 prl.pricing_attribute9,
1476 prl.pricing_attribute10,
1477 prl.pricing_attribute11,
1478 prl.pricing_attribute12,
1479 prl.pricing_attribute13,
1480 prl.pricing_attribute14,
1481 prl.pricing_attribute15,
1482 prl.start_date_active,
1483 prl.end_date_active,
1484 prl.context,
1485 prl.attribute1,
1486 prl.attribute2,
1487 prl.attribute3,
1488 prl.attribute4,
1489 prl.attribute5,
1490 prl.attribute6,
1491 prl.attribute7,
1492 prl.attribute8,
1493 prl.attribute9,
1494 prl.attribute10,
1495 prl.attribute11,
1496 prl.attribute12,
1497 prl.attribute13,
1498 prl.attribute14,
1499 prl.attribute15,
1500 prl.price_list_line_id,
1501 'AGR',
1502 3
1503 from qp_list_headers_b qph,
1504 so_price_list_lines_115 prl
1505 where prl.price_list_id = qph.list_header_id
1506 and prl.price_list_id in ( select price_list_id
1507 from so_agreements_b)
1508 and not exists ( select null
1509 from qp_LIST_lines
1510 where list_line_id = prl.price_list_line_id )
1511 comment start
1512 and not exists ( select null
1513 from qp_discount_mapping
1517 and prl.price_list_line_id between l_min_line and l_max_line;
1514 where old_discount_line_id = prl.price_list_line_id
1515 and new_type = 'P')
1516 comment end
1518
1519 vivek */
1520
1521 /* vivek
1522
1523 cursor agr_prc_list is
1524 select distinct price_list_id
1525 from so_agreements_b;
1526
1527 cursor prc_list_mapping is
1528 select old_discount_id, new_list_header_id
1529 from qp_discount_mapping
1530 where new_type = 'P'
1531 and old_discount_line_id is null;
1532
1533 vivek */
1534
1535 CURSOR sec_prc_list IS
1536 SELECT DISTINCT secondary_price_list_id
1537 FROM SO_PRICE_LISTS_B where secondary_price_list_id IS NOT NULL;
1538
1539 begin
1540
1541 -- qp_util.log_error(NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1542 -- NULL, 'MAIN-0', v_errortext, 'PRICE_LISTS');
1543
1544
1545 BEGIN
1546
1547 qp_util.get_segs_for_flex(flexfield_name => 'QP_ATTR_DEFNS_PRICING',
1548 application_short_name => 'QP',
1549 x_segs_upg_t => v_segs_upg_t,
1550 error_code => l_error);
1551
1552 IF l_error <> 0 THEN
1553 RAISE e_get_prod_flex_properties;
1554 END IF;
1555
1556 EXCEPTION
1557
1558 WHEN e_get_prod_flex_properties THEN
1559 null;
1560 err_num := SQLCODE;
1561 err_msg := 'GET_SEGS_FOR_FLEX';
1562 rollback;
1563 qp_util.log_error(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1564 'GET_SEGS_FOR_FLEX', err_msg, 'PRICE_LISTS');
1565 RAISE;
1566
1567
1568 END;
1569
1570
1571 --dbms_output.put_line('crll 1');
1572
1573 l_line_count := 0;
1574
1575 /* vivek
1576
1577 for agr_prc_list_rec in prc_list_mapping loop
1578
1579 agr_old_price_lists(agr_prc_list_rec.old_discount_id) := agr_prc_list_rec.old_discount_id;
1580 agr_new_list_headers(agr_prc_list_rec.old_discount_id) := agr_prc_list_rec.new_list_header_id;
1581
1582 end loop;
1583
1584 vivek */
1585
1586 FOR sec_prc_list_rec IN sec_prc_list LOOP
1587 sec_prc_list_ids(sec_prc_list_rec.secondary_price_list_id) := sec_prc_list_rec.secondary_price_list_id;
1588 END LOOP;
1589
1590 begin
1591
1592 select start_line_id,
1593 end_line_id
1594 into v_min_line,
1595 v_max_line
1596 from qp_upg_lines_distribution
1597 where worker = l_worker
1598 and line_type = 'PLL';
1599
1600 exception
1601
1602 when no_data_found then
1603
1604 /* log the error */
1605 v_min_line := 0;
1606 v_max_line := 0;
1607 commit;
1608 return;
1609 end;
1610
1611 --dbms_output.put_line('v_min_line : ' || v_min_line);
1612 --dbms_output.put_line('v_max_line : ' || v_max_line);
1613 --dbms_output.put_line('rowcount 0 is : ' || sql%rowcount );
1614
1615 /*
1616 FOR agr_prc_list_rec in agr_prc_list loop
1617
1618 agr_prc_lists(agr_prc_list_rec.price_list_id) := agr_prc_list_rec.price_list_id;
1619
1620 end loop;
1621 */
1622
1623
1624
1625
1626 --l_max_line := nvl(price_list_line_ids.FIRST,1) -1;
1627
1628 IF mod( (v_max_line - v_min_line),p_batchsize) > 0 then
1629 j := 1;
1630 END IF;
1631
1632 /*
1633 IF mod(nvl(price_list_line_ids.LAST,0),p_batchsize) > 0 then
1634 j := 1;
1635 END IF;
1636 */
1637
1638 numiterations := trunc((v_max_line-v_min_line)/p_batchsize) + j ;
1639 l_min_line := 0;
1640 l_max_line := v_min_line-1;
1641
1642 -- qp_util.log_error(NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1643 -- NULL, 'MAIN-0.5', v_errortext, 'PRICE_LISTS');
1644
1645
1646 WHILE ( numiterations > 0)
1647 LOOP
1648
1649 -- qp_util.log_error(NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1650 -- NULL, 'MAIN-1', v_errortext, 'PRICE_LISTS');
1651
1652 --dbms_output.put_line('v_max_line 3 : ' || v_max_line);
1653
1654 l_min_line := l_max_line + 1;
1655
1656 IF (numiterations > 1) then
1657
1658 l_max_line := l_min_line + p_batchsize -1;
1659 numiterations := numiterations -1;
1660
1661 ELSE
1662
1663 l_max_line := v_max_line;
1664 numiterations := numiterations -1;
1665
1666 END IF;
1667
1668
1669
1670 BEGIN /* begin for bulk fetch */
1671
1672 OPEN prc_list_line;
1673
1674 --dbms_output.put_line('v_max_line 1 : ' || v_max_line);
1675
1676 FETCH prc_list_line BULK COLLECT INTO
1677 price_list_line_ids,
1678 creation_dates,
1679 created_bys,
1680 last_update_dates,
1681 last_updated_bys,
1682 last_update_logins,
1683 program_application_ids,
1684 program_ids,
1685 program_update_dates,
1686 request_ids,
1687 price_list_ids,
1688 inventory_item_ids,
1689 unit_codes,
1690 method_codes,
1691 list_prices,
1692 pricing_rule_ids,
1693 reprice_flags,
1694 pricing_contexts,
1695 pricing_attribute1s,
1696 pricing_attribute2s,
1697 pricing_attribute3s,
1698 pricing_attribute4s,
1699 pricing_attribute5s,
1700 pricing_attribute6s,
1701 pricing_attribute7s,
1702 pricing_attribute8s,
1703 pricing_attribute9s,
1704 pricing_attribute10s,
1705 pricing_attribute11s,
1709 pricing_attribute15s,
1706 pricing_attribute12s,
1707 pricing_attribute13s,
1708 pricing_attribute14s,
1710 start_date_actives,
1711 end_date_actives,
1712 contexts,
1713 attribute1s,
1714 attribute2s,
1715 attribute3s,
1716 attribute4s,
1717 attribute5s,
1718 attribute6s,
1719 attribute7s,
1720 attribute8s,
1721 attribute9s,
1722 attribute10s,
1723 attribute11s,
1724 attribute12s,
1725 attribute13s,
1726 attribute14s,
1727 attribute15s,
1728 old_price_list_line_ids,
1729 agr_types,
1730 qualification_ind;
1731
1732
1733 EXCEPTION
1734
1735 WHEN NO_DATA_FOUND THEN
1736
1737 --dbms_output.put_line('no lines to process - done ');
1738 NULL;
1739
1740
1741 END;
1742
1743 CLOSE prc_list_line;
1744
1745
1746 -- qp_util.log_error(NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1747 -- NULL, 'MAIN-2', v_errortext, 'PRICE_LISTS');
1748
1749 IF price_list_line_ids.FIRST is not null THEN
1750
1751 start_prc_list_line_id := nvl(price_list_line_ids.FIRST,0);
1752 end_prc_list_line_id := nvl(price_list_line_ids.LAST,0);
1753
1754
1755 FOR K in start_prc_list_line_id..end_prc_list_line_id loop
1756
1757 /* vivek
1758
1759 IF ( agr_old_price_lists.exists(price_list_ids(K))
1760 and ( agr_types(K) is not null
1761 and agr_types(K) = 'AGR') ) then
1762
1763 select qp_list_lines_s.nextval
1764 into new_prc_list_line_id
1765 from dual;
1766
1767 price_list_line_ids(K) := new_prc_list_line_id;
1768
1769
1770 prc_list_maps(price_list_line_ids(K)).old_price_list_id := price_list_ids(K);
1771
1772 price_list_ids(K) := agr_new_list_headers(price_list_ids(K));
1773
1774 prc_list_maps(price_list_line_ids(K)).new_list_header_id := price_list_ids(K);
1775
1776 prc_list_maps(price_list_line_ids(K)).old_price_list_line_id := old_price_list_line_ids(K);
1777
1778 prc_list_maps(price_list_line_ids(K)).new_list_line_id := price_list_line_ids(K);
1779
1780 prc_list_maps(price_list_line_ids(K)).db_flag := 'N';
1781
1782 qualification_ind(K) := 1;
1783
1784
1785 END IF;
1786
1787 vivek */
1788
1789 IF (sec_prc_list_ids.exists(price_list_ids(k))) THEN
1790 qualification_ind(k) := 3;
1791 END IF;
1792
1793 END LOOP;
1794
1795 -- qp_util.log_error(NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1796 -- NULL, 'MAIN-3', v_errortext, 'PRICE_LISTS');
1797
1798 --dbms_output.put_line('rowcount is : ' || sql%rowcount );
1799
1800
1801
1802 --dbms_output.put_line('v_max_line 2 : ' || v_max_line);
1803
1804
1805 l_product_context := 'ITEM';
1806 l_product_attr := 'PRICING_ATTRIBUTE1';
1807
1808 qp_util.get_segs_flex_precedence(p_segs_upg_t => v_segs_upg_t,
1809 p_context => l_product_context,
1810 p_attribute => l_product_attr,
1811 x_precedence => l_prod_precedence,
1812 x_datatype => l_prod_datatype);
1813
1814
1815
1816
1817 BEGIN /* forall k in 1_min_line..l_max_line */
1818
1819 FORALL K IN start_prc_list_line_id..end_prc_list_line_id
1820 insert into qp_LIST_lines(
1821 LIST_LINE_ID,
1822 LIST_LINE_NO,
1823 CREATION_DATE,
1824 CREATED_BY,
1825 LAST_UPDATE_DATE,
1826 LAST_UPDATED_BY,
1827 LAST_UPDATE_LOGIN,
1828 PROGRAM_APPLICATION_ID,
1829 PROGRAM_ID,
1830 PROGRAM_UPDATE_DATE,
1831 REQUEST_ID,
1832 LIST_HEADER_ID,
1833 LIST_LINE_TYPE_CODE,
1834 START_DATE_ACTIVE, /* START_DATE_EFFECTIVE */
1835 END_DATE_ACTIVE, /* END_DATE_EFFECTIVE */
1836 AUTOMATIC_FLAG,
1837 MODIFIER_LEVEL_CODE,
1838 LIST_PRICE,
1839 LIST_PRICE_UOM_CODE,
1840 PRIMARY_UOM_FLAG,
1841 INVENTORY_ITEM_ID,
1842 ORGANIZATION_ID,
1843 RELATED_ITEM_ID,
1844 RELATIONSHIP_TYPE_ID,
1845 SUBSTITUTION_CONTEXT,
1846 SUBSTITUTION_ATTRIBUTE,
1847 SUBSTITUTION_VALUE,
1848 REVISION,
1849 REVISION_DATE,
1850 REVISION_REASON_CODE,
1851 CONTEXT,
1852 ATTRIBUTE1,
1853 ATTRIBUTE2,
1854 ATTRIBUTE3,
1855 ATTRIBUTE4,
1856 ATTRIBUTE5,
1857 ATTRIBUTE6,
1858 ATTRIBUTE7,
1859 ATTRIBUTE8,
1860 ATTRIBUTE9,
1861 ATTRIBUTE10,
1862 ATTRIBUTE11,
1863 ATTRIBUTE12,
1864 ATTRIBUTE13,
1865 ATTRIBUTE14,
1866 ATTRIBUTE15,
1867 COMMENTS,
1868 PRICE_BREAK_TYPE_CODE,
1869 PERCENT_PRICE,
1870 EFFECTIVE_PERIOD_UOM,
1871 NUMBER_EFFECTIVE_PERIODS,
1872 OPERAND,
1873 ARITHMETIC_OPERATOR,
1874 OVERRIDE_FLAG,
1875 PRINT_ON_INVOICE_FLAG,
1876 REBATE_TRANSACTION_TYPE_CODE,
1877 ESTIM_ACCRUAL_RATE,
1878 PRICE_BY_FORMULA_ID,
1879 GENERATE_USING_FORMULA_ID,
1880 PRICING_PHASE_ID,
1881 PRICING_GROUP_SEQUENCE,
1882 ACCRUAL_FLAG,
1883 PRODUCT_PRECEDENCE,
1884 INCOMPATIBILITY_GRP_CODE,
1885 QUALIFICATION_IND
1886 --ENH Upgrade BOAPI for orig_sys...ref RAVI
1887 ,ORIG_SYS_LINE_REF
1888 ,ORIG_SYS_HEADER_REF
1889 )
1890 values(
1891 price_list_line_ids(K),
1892 price_list_line_ids(K),
1893 creation_dates(K),
1894 created_bys(K),
1895 last_update_dates(K),
1896 last_updated_bys(K),
1900 program_update_dates(K),
1897 last_update_logins(K),
1898 program_application_ids(K),
1899 program_ids(K),
1901 request_ids(K),
1902 price_list_ids(K),
1903 'PLL',
1904 start_date_actives(K), /* no need to do nvl */
1905 end_date_actives(K),
1906 'Y',
1907 'LINE',
1908 DECODE(method_codes(K), 'AMNT',list_prices(K),NULL),
1909 unit_codes(K),
1910 l_primary_uom_flag,
1911 NULL,
1912 NULL,
1913 NULL,
1914 NULL,
1915 NULL,
1916 NULL,
1917 NULL,
1918 NULL,
1919 NULL,
1920 NULL,
1921 contexts(K),
1922 attribute1s(K),
1923 attribute2s(K),
1924 attribute3s(K),
1925 attribute4s(K),
1926 attribute5s(K),
1927 attribute6s(K),
1928 attribute7s(K),
1929 attribute8s(K),
1930 attribute9s(K),
1931 attribute10s(K),
1932 attribute11s(K),
1933 attribute12s(K),
1934 attribute13s(K),
1935 attribute14s(K),
1936 attribute15s(K),
1937 NULL,
1938 NULL,
1939 DECODE(method_codes(K), 'PERC', list_prices(K), NULL),
1940 NULL,
1941 NULL,
1942 list_prices(K),
1943 DECODE(method_codes(K), 'PERC', 'PERCENT_PRICE',
1944 'AMNT', 'UNIT_PRICE', NULL),
1945 NULL,
1946 'N',
1947 NULL,
1948 NULL,
1949 NULL,
1950 pricing_rule_ids(K),
1951 1,
1952 0,
1953 'N',
1954 l_prod_precedence,
1955 'EXCL',
1956 qualification_ind(k)
1957 --ENH Upgrade BOAPI for orig_sys...ref RAVI
1958 ,to_char(price_list_line_ids(K))
1959 ,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=price_list_ids(K))
1960 );
1961
1962 EXCEPTION
1963 WHEN OTHERS THEN
1964 v_errortext := SUBSTR(SQLERRM, 1,240);
1965 K := sql%rowcount + start_prc_list_line_id;
1966 ROLLBACK;
1967 /*
1968 qp_util.log_error(price_list_ids(K),
1969 price_list_line_ids(K),
1970 NULL, NULL, NULL, NULL, NULL,
1971 NULL, 'PRICE_LIST_LINES', v_errortext, 'PRICE_LISTS');
1972 */
1973 RAISE;
1974 END; /* exception handling for forall k in l_min_line .. l_max_line */
1975
1976 -- qp_util.log_error(NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1977 -- NULL, 'MAIN-5', v_errortext, 'PRICE_LISTS');
1978
1979 --dbms_output.put_line('crpa 1');
1980
1981 attr_count := 0;
1982
1983 /* delete the attr_<field_name> tables */
1984
1985 attr_creation_dates.delete;
1986 attr_created_bys.delete;
1987 attr_last_update_dates.delete;
1988 attr_last_updated_bys.delete;
1989 attr_last_update_logins.delete;
1990 attr_program_application_ids.delete;
1991 attr_program_ids.delete;
1992 attr_program_update_dates.delete;
1993 attr_request_ids.delete;
1994 attr_list_line_ids.delete;
1995 attr_excluder_flags.delete;
1996 attr_accumulate_flags.delete;
1997 attr_product_attr_contexts.delete;
1998 attr_product_attributes.delete;
1999 attr_product_attr_values.delete;
2000 attr_product_uom_codes.delete;
2001 attr_comparison_operator_codes.delete;
2002 attr_pricing_contexts.delete;
2003 attr_pricing_attrs.delete;
2004 attr_pricing_attr_value_froms.delete;
2005 attr_pricing_attr_value_tos.delete;
2006 prc_datatypes.delete;
2007
2008 FOR K IN start_prc_list_line_id..end_prc_list_line_id loop
2009
2010 attr_count := attr_count + 1;
2011
2012 l_product_context := 'ITEM';
2013 l_product_attr := 'PRICING_ATTRIBUTE1';
2014
2015 --dbms_output.put_line('crpa 4');
2016
2017 attr_creation_dates(attr_count) := creation_dates(K);
2018 attr_created_bys(attr_count) := created_bys(K);
2019 attr_last_update_dates(attr_count) := last_update_dates(K);
2020 attr_last_updated_bys(attr_count) := last_updated_bys(K);
2021 attr_last_update_logins(attr_count) := last_update_logins(K);
2022 attr_program_application_ids(attr_count) := program_application_ids(K);
2023 attr_program_ids(attr_count) := program_ids(K);
2024 attr_program_update_dates(attr_count) := program_update_dates(K);
2025 attr_request_ids(attr_count) := request_ids(K);
2026 attr_list_line_ids(attr_count) := price_list_line_ids(K);
2027 attr_excluder_flags(attr_count) := 'N';
2028 attr_accumulate_flags(attr_count) := 'N';
2029 attr_product_attr_contexts(attr_count) := l_product_context;
2030 attr_product_attributes(attr_count) := l_product_attr;
2031 attr_product_attr_values(attr_count) := inventory_item_ids(K);
2032 attr_product_uom_codes(attr_count) := unit_codes(K);
2033 attr_comparison_operator_codes(attr_count) := 'BETWEEN'; --2664220 --NULL;
2034 attr_pricing_contexts(attr_count) := NULL;
2035 attr_pricing_attrs(attr_count) := NULL;
2036 attr_pricing_attr_value_froms(attr_count) := NULL;
2037 attr_pricing_attr_value_tos(attr_count) := NULL;
2038 prc_datatypes(attr_count) := NULL;
2039
2040 --dbms_output.put_line('crpa 101');
2041
2042 /* check if pricing_attribute_context is present*/
2043
2044 /* Commented attr_pricing_contexts(attr_count) := pricing_contexts(K). This is because of Honeywell Bug # 1731134 - vgulati */
2045
2046 If pricing_contexts(K) is not null then
2047 -- attr_pricing_contexts(attr_count) := pricing_contexts(K);
2048 l_pricing_context := pricing_contexts(K);
2049
2053
2050 --l_pricing_attr_rec.l_pricing_context := pricing_contexts(K);
2051
2052 else
2054 l_pricing_context := 'Upgrade Context';
2055
2056 end if;
2057
2058 /* added attr_count := attr_count + 1 to create an extra record containing only product in qp_pricing_attributes. This is because of Honeywell Bug # 1731134 - vgulati */
2059
2060 If pricing_attribute1s(K) is not null then
2061 attr_count := attr_count + 1;
2062 attr_creation_dates(attr_count) := creation_dates(K);
2063 attr_created_bys(attr_count) := created_bys(K);
2064 attr_last_update_dates(attr_count) := last_update_dates(K);
2065 attr_last_updated_bys(attr_count) := last_updated_bys(K);
2066 attr_last_update_logins(attr_count) := last_update_logins(K);
2067 attr_program_application_ids(attr_count) := program_application_ids(K);
2068 attr_program_ids(attr_count) := program_ids(K);
2069 attr_program_update_dates(attr_count) := program_update_dates(K);
2070 attr_request_ids(attr_count) := request_ids(K);
2071 attr_list_line_ids(attr_count) := price_list_line_ids(K);
2072 attr_excluder_flags(attr_count) := 'N';
2073 attr_accumulate_flags(attr_count) := 'N';
2074 attr_product_attr_contexts(attr_count) := l_product_context;
2075 attr_product_attributes(attr_count) := l_product_attr;
2076 attr_product_attr_values(attr_count) := inventory_item_ids(K);
2077 attr_product_uom_codes(attr_count) := unit_codes(K);
2078 attr_comparison_operator_codes(attr_count) := '='; --3251389 --NULL;
2079 attr_pricing_contexts(attr_count) := l_pricing_context;
2080 attr_pricing_attrs(attr_count) := 'PRICING_ATTRIBUTE1';
2081 attr_pricing_attr_value_froms(attr_count) := pricing_attribute1s(K);
2082 attr_pricing_attr_value_tos(attr_count) := NULL;
2083 qp_util.get_segs_flex_precedence(p_segs_upg_t => v_segs_upg_t,
2084 p_context => attr_pricing_contexts(attr_count),
2085 p_attribute => attr_pricing_attrs(attr_count),
2086 x_precedence => l_prc_precedence,
2087 x_datatype => prc_datatypes(attr_count));
2088
2089 end if;
2090
2091 --dbms_output.put_line('crpa 102');
2092
2093 If pricing_attribute2s(K) is not null then
2094 attr_count := attr_count + 1;
2095 attr_creation_dates(attr_count) := creation_dates(K);
2096 attr_created_bys(attr_count) := created_bys(K);
2097 attr_last_update_dates(attr_count) := last_update_dates(K);
2098 attr_last_updated_bys(attr_count) := last_updated_bys(K);
2099 attr_last_update_logins(attr_count) := last_update_logins(K);
2100 attr_program_application_ids(attr_count) := program_application_ids(K);
2101 attr_program_ids(attr_count) := program_ids(K);
2102 attr_program_update_dates(attr_count) := program_update_dates(K);
2103 attr_request_ids(attr_count) := request_ids(K);
2104 attr_list_line_ids(attr_count) := price_list_line_ids(K);
2105 attr_excluder_flags(attr_count) := 'N';
2106 attr_accumulate_flags(attr_count) := 'N';
2107 attr_product_attr_contexts(attr_count) := l_product_context;
2108 attr_product_attributes(attr_count) := l_product_attr;
2109 attr_product_attr_values(attr_count) := inventory_item_ids(K);
2110 attr_product_uom_codes(attr_count) := unit_codes(K);
2111 attr_comparison_operator_codes(attr_count) := '='; --3251389 --NULL;
2112 attr_pricing_contexts(attr_count) := l_pricing_context;
2113 attr_pricing_attrs(attr_count) := 'PRICING_ATTRIBUTE2';
2114 attr_pricing_attr_value_froms(attr_count) := pricing_attribute2s(K);
2115 attr_pricing_attr_value_tos(attr_count) := NULL;
2116 qp_util.get_segs_flex_precedence(p_segs_upg_t => v_segs_upg_t,
2117 p_context => attr_pricing_contexts(attr_count),
2118 p_attribute => attr_pricing_attrs(attr_count),
2119 x_precedence => l_prc_precedence,
2120 x_datatype => prc_datatypes(attr_count));
2121
2122 /*
2123 BEGIN
2124 qp_util.get_prod_flex_properties
2125 ( attr_pricing_contexts(attr_count),
2126 attr_pricing_attrs(attr_count),
2127 attr_pricing_attr_value_froms(attr_count),
2128 prc_datatypes(attr_count), l_prc_precedence, l_error);
2129 IF l_error <> 0 THEN
2130 RAISE e_get_prod_flex_properties;
2131 END IF;
2132 EXCEPTION
2133 WHEN e_get_prod_flex_properties THEN
2134 err_num := SQLCODE;
2135 err_msg := SUBSTR(SQLERRM, 1, 240);
2136 qp_util.log_error(price_list_ids(K), price_list_line_ids(K),
2137 NULL, NULL, NULL, NULL, NULL, NULL,
2138 'GET_PROD_FLEX_PRC', err_msg, 'PRICE_LISTS');
2139 END;
2140 */
2141 --l_pricing_attr_tbl(I) := l_pricing_attr_rec;
2142 end if;
2143
2144 --dbms_output.put_line('crpa 103');
2145
2146 If pricing_attribute3s(K) is not null then
2147 attr_count := attr_count + 1;
2148 attr_creation_dates(attr_count) := creation_dates(K);
2149 attr_created_bys(attr_count) := created_bys(K);
2150 attr_last_update_dates(attr_count) := last_update_dates(K);
2151 attr_last_updated_bys(attr_count) := last_updated_bys(K);
2152 attr_last_update_logins(attr_count) := last_update_logins(K);
2153 attr_program_application_ids(attr_count) := program_application_ids(K);
2154 attr_program_ids(attr_count) := program_ids(K);
2158 attr_excluder_flags(attr_count) := 'N';
2155 attr_program_update_dates(attr_count) := program_update_dates(K);
2156 attr_request_ids(attr_count) := request_ids(K);
2157 attr_list_line_ids(attr_count) := price_list_line_ids(K);
2159 attr_accumulate_flags(attr_count) := 'N';
2160 attr_product_attr_contexts(attr_count) := l_product_context;
2161 attr_product_attributes(attr_count) := l_product_attr;
2162 attr_product_attr_values(attr_count) := inventory_item_ids(K);
2163 attr_product_uom_codes(attr_count) := unit_codes(K);
2164 attr_comparison_operator_codes(attr_count) := '='; --3251389 --NULL;
2165 attr_pricing_contexts(attr_count) := l_pricing_context;
2166 attr_pricing_attrs(attr_count) := 'PRICING_ATTRIBUTE3';
2167 attr_pricing_attr_value_froms(attr_count) := pricing_attribute3s(K);
2168 attr_pricing_attr_value_tos(attr_count) := NULL;
2169 qp_util.get_segs_flex_precedence(p_segs_upg_t => v_segs_upg_t,
2170 p_context => attr_pricing_contexts(attr_count),
2171 p_attribute => attr_pricing_attrs(attr_count),
2172 x_precedence => l_prc_precedence,
2173 x_datatype => prc_datatypes(attr_count));
2174
2175 /*
2176 --l_pricing_attr_tbl(I) := l_pricing_attr_rec;
2177 BEGIN
2178 qp_util.get_prod_flex_properties
2179 ( attr_pricing_contexts(attr_count),
2180 attr_pricing_attrs(attr_count),
2181 attr_pricing_attr_value_froms(attr_count),
2182 prc_datatypes(attr_count), l_prc_precedence, l_error);
2183 IF l_error <> 0 THEN
2184 RAISE e_get_prod_flex_properties;
2185 END IF;
2186 EXCEPTION
2187 WHEN e_get_prod_flex_properties THEN
2188 err_num := SQLCODE;
2189 err_msg := SUBSTR(SQLERRM, 1, 240);
2190 qp_util.log_error(price_list_ids(K), price_list_line_ids(K),
2191 NULL, NULL, NULL, NULL, NULL, NULL,
2192 'GET_PROD_FLEX_PRC', err_msg, 'PRICE_LISTS');
2193 END;
2194 */
2195
2196 end if;
2197
2198 --dbms_output.put_line('crpa 104');
2199
2200 If pricing_attribute4s(K) is not null then
2201 attr_count := attr_count + 1;
2202 attr_creation_dates(attr_count) := creation_dates(K);
2203 attr_created_bys(attr_count) := created_bys(K);
2204 attr_last_update_dates(attr_count) := last_update_dates(K);
2205 attr_last_updated_bys(attr_count) := last_updated_bys(K);
2206 attr_last_update_logins(attr_count) := last_update_logins(K);
2207 attr_program_application_ids(attr_count) := program_application_ids(K);
2208 attr_program_ids(attr_count) := program_ids(K);
2209 attr_program_update_dates(attr_count) := program_update_dates(K);
2210 attr_request_ids(attr_count) := request_ids(K);
2211 attr_list_line_ids(attr_count) := price_list_line_ids(K);
2212 attr_excluder_flags(attr_count) := 'N';
2213 attr_accumulate_flags(attr_count) := 'N';
2214 attr_product_attr_contexts(attr_count) := l_product_context;
2215 attr_product_attributes(attr_count) := l_product_attr;
2216 attr_product_attr_values(attr_count) := inventory_item_ids(K);
2217 attr_product_uom_codes(attr_count) := unit_codes(K);
2218 attr_comparison_operator_codes(attr_count) := '='; --3251389 --NULL;
2219 attr_pricing_contexts(attr_count) := l_pricing_context;
2220 attr_pricing_attrs(attr_count) := 'PRICING_ATTRIBUTE4';
2221 attr_pricing_attr_value_froms(attr_count) := pricing_attribute4s(K);
2222 attr_pricing_attr_value_tos(attr_count) := NULL;
2223 qp_util.get_segs_flex_precedence(p_segs_upg_t => v_segs_upg_t,
2224 p_context => attr_pricing_contexts(attr_count),
2225 p_attribute => attr_pricing_attrs(attr_count),
2226 x_precedence => l_prc_precedence,
2227 x_datatype => prc_datatypes(attr_count));
2228 /*
2229 BEGIN
2230 qp_util.get_prod_flex_properties
2231 ( attr_pricing_contexts(attr_count),
2232 attr_pricing_attrs(attr_count),
2233 attr_pricing_attr_value_froms(attr_count),
2234 prc_datatypes(attr_count), l_prc_precedence, l_error);
2235 IF l_error <> 0 THEN
2236 RAISE e_get_prod_flex_properties;
2237 END IF;
2238 EXCEPTION
2239 WHEN e_get_prod_flex_properties THEN
2240 err_num := SQLCODE;
2241 err_msg := SUBSTR(SQLERRM, 1, 240);
2242 qp_util.log_error(price_list_ids(K), price_list_line_ids(K),
2243 NULL, NULL, NULL, NULL, NULL, NULL,
2244 'GET_PROD_FLEX_PRC', err_msg, 'PRICE_LISTS');
2245 END;
2246 --l_pricing_attr_tbl(I) := l_pricing_attr_rec;
2247 */
2248
2249 end if;
2250
2251 --dbms_output.put_line('crpa 105');
2252
2253 If pricing_attribute5s(K) is not null then
2254 attr_count := attr_count + 1;
2255 attr_creation_dates(attr_count) := creation_dates(K);
2256 attr_created_bys(attr_count) := created_bys(K);
2257 attr_last_update_dates(attr_count) := last_update_dates(K);
2258 attr_last_updated_bys(attr_count) := last_updated_bys(K);
2259 attr_last_update_logins(attr_count) := last_update_logins(K);
2260 attr_program_application_ids(attr_count) := program_application_ids(K);
2264 attr_list_line_ids(attr_count) := price_list_line_ids(K);
2261 attr_program_ids(attr_count) := program_ids(K);
2262 attr_program_update_dates(attr_count) := program_update_dates(K);
2263 attr_request_ids(attr_count) := request_ids(K);
2265 attr_excluder_flags(attr_count) := 'N';
2266 attr_accumulate_flags(attr_count) := 'N';
2267 attr_product_attr_contexts(attr_count) := l_product_context;
2268 attr_product_attributes(attr_count) := l_product_attr;
2269 attr_product_attr_values(attr_count) := inventory_item_ids(K);
2270 attr_product_uom_codes(attr_count) := unit_codes(K);
2271 attr_comparison_operator_codes(attr_count) := '='; --3251389 --NULL;
2272 attr_pricing_contexts(attr_count) := l_pricing_context;
2273 attr_pricing_attrs(attr_count) := 'PRICING_ATTRIBUTE5';
2274 attr_pricing_attr_value_froms(attr_count) := pricing_attribute5s(K);
2275 attr_pricing_attr_value_tos(attr_count) := NULL;
2276 qp_util.get_segs_flex_precedence(p_segs_upg_t => v_segs_upg_t,
2277 p_context => attr_pricing_contexts(attr_count),
2278 p_attribute => attr_pricing_attrs(attr_count),
2279 x_precedence => l_prc_precedence,
2280 x_datatype => prc_datatypes(attr_count));
2281 /*
2282 BEGIN
2283 qp_util.get_prod_flex_properties
2284 ( attr_pricing_contexts(attr_count),
2285 attr_pricing_attrs(attr_count),
2286 attr_pricing_attr_value_froms(attr_count),
2287 prc_datatypes(attr_count), l_prc_precedence, l_error);
2288 IF l_error <> 0 THEN
2289 RAISE e_get_prod_flex_properties;
2290 END IF;
2291 EXCEPTION
2292 WHEN e_get_prod_flex_properties THEN
2293 err_num := SQLCODE;
2294 err_msg := SUBSTR(SQLERRM, 1, 240);
2295 qp_util.log_error(price_list_ids(K), price_list_line_ids(K),
2296 NULL, NULL, NULL, NULL, NULL, NULL,
2297 'GET_PROD_FLEX_PRC', err_msg, 'PRICE_LISTS');
2298 END;
2299 --l_pricing_attr_tbl(I) := l_pricing_attr_rec;
2300 */
2301
2302 end if;
2303
2304 --dbms_output.put_line('crpa 106');
2305
2306 If pricing_attribute6s(K) is not null then
2307 attr_count := attr_count + 1;
2308 attr_creation_dates(attr_count) := creation_dates(K);
2309 attr_created_bys(attr_count) := created_bys(K);
2310 attr_last_update_dates(attr_count) := last_update_dates(K);
2311 attr_last_updated_bys(attr_count) := last_updated_bys(K);
2312 attr_last_update_logins(attr_count) := last_update_logins(K);
2313 attr_program_application_ids(attr_count) := program_application_ids(K);
2314 attr_program_ids(attr_count) := program_ids(K);
2315 attr_program_update_dates(attr_count) := program_update_dates(K);
2316 attr_request_ids(attr_count) := request_ids(K);
2317 attr_list_line_ids(attr_count) := price_list_line_ids(K);
2318 attr_excluder_flags(attr_count) := 'N';
2319 attr_accumulate_flags(attr_count) := 'N';
2320 attr_product_attr_contexts(attr_count) := l_product_context;
2321 attr_product_attributes(attr_count) := l_product_attr;
2322 attr_product_attr_values(attr_count) := inventory_item_ids(K);
2323 attr_product_uom_codes(attr_count) := unit_codes(K);
2324 attr_comparison_operator_codes(attr_count) := '='; --3251389 --NULL;
2325 attr_pricing_contexts(attr_count) := l_pricing_context;
2326 attr_pricing_attrs(attr_count) := 'PRICING_ATTRIBUTE6';
2327 attr_pricing_attr_value_froms(attr_count) := pricing_attribute6s(K);
2328 attr_pricing_attr_value_tos(attr_count) := NULL;
2329 qp_util.get_segs_flex_precedence(p_segs_upg_t => v_segs_upg_t,
2330 p_context => attr_pricing_contexts(attr_count),
2331 p_attribute => attr_pricing_attrs(attr_count),
2332 x_precedence => l_prc_precedence,
2333 x_datatype => prc_datatypes(attr_count));
2334 /*
2335 BEGIN
2336 qp_util.get_prod_flex_properties
2337 ( attr_pricing_contexts(attr_count),
2338 attr_pricing_attrs(attr_count),
2339 attr_pricing_attr_value_froms(attr_count),
2340 prc_datatypes(attr_count), l_prc_precedence, l_error);
2341 IF l_error <> 0 THEN
2342 RAISE e_get_prod_flex_properties;
2343 END IF;
2344 EXCEPTION
2345 WHEN e_get_prod_flex_properties THEN
2346 err_num := SQLCODE;
2347 err_msg := SUBSTR(SQLERRM, 1, 240);
2348 qp_util.log_error(price_list_ids(K), price_list_line_ids(K),
2349 NULL, NULL, NULL, NULL, NULL, NULL,
2350 'GET_PROD_FLEX_PRC', err_msg, 'PRICE_LISTS');
2351 END;
2352 --l_pricing_attr_tbl(I) := l_pricing_attr_rec;
2353 */
2354
2355 end if;
2356
2357 --dbms_output.put_line('crpa 107');
2358
2359 If pricing_attribute7s(K) is not null then
2360 attr_count := attr_count + 1;
2361 attr_creation_dates(attr_count) := creation_dates(K);
2362 attr_created_bys(attr_count) := created_bys(K);
2363 attr_last_update_dates(attr_count) := last_update_dates(K);
2364 attr_last_updated_bys(attr_count) := last_updated_bys(K);
2365 attr_last_update_logins(attr_count) := last_update_logins(K);
2366 attr_program_application_ids(attr_count) := program_application_ids(K);
2367 attr_program_ids(attr_count) := program_ids(K);
2368 attr_program_update_dates(attr_count) := program_update_dates(K);
2369 attr_request_ids(attr_count) := request_ids(K);
2373 attr_product_attr_contexts(attr_count) := l_product_context;
2370 attr_list_line_ids(attr_count) := price_list_line_ids(K);
2371 attr_excluder_flags(attr_count) := 'N';
2372 attr_accumulate_flags(attr_count) := 'N';
2374 attr_product_attributes(attr_count) := l_product_attr;
2375 attr_product_attr_values(attr_count) := inventory_item_ids(K);
2376 attr_product_uom_codes(attr_count) := unit_codes(K);
2377 attr_comparison_operator_codes(attr_count) := '='; --3251389 --NULL;
2378 attr_pricing_contexts(attr_count) := l_pricing_context;
2379 attr_pricing_attrs(attr_count) := 'PRICING_ATTRIBUTE7';
2380 attr_pricing_attr_value_froms(attr_count) := pricing_attribute7s(K);
2381 attr_pricing_attr_value_tos(attr_count) := NULL;
2382 qp_util.get_segs_flex_precedence(p_segs_upg_t => v_segs_upg_t,
2383 p_context => attr_pricing_contexts(attr_count),
2384 p_attribute => attr_pricing_attrs(attr_count),
2385 x_precedence => l_prc_precedence,
2386 x_datatype => prc_datatypes(attr_count));
2387 /*
2388 BEGIN
2389 qp_util.get_prod_flex_properties
2390 ( attr_pricing_contexts(attr_count),
2391 attr_pricing_attrs(attr_count),
2392 attr_pricing_attr_value_froms(attr_count),
2393 prc_datatypes(attr_count), l_prc_precedence, l_error);
2394 IF l_error <> 0 THEN
2395 RAISE e_get_prod_flex_properties;
2396 END IF;
2397 EXCEPTION
2398 WHEN e_get_prod_flex_properties THEN
2399 err_num := SQLCODE;
2400 err_msg := SUBSTR(SQLERRM, 1, 240);
2401 qp_util.log_error(price_list_ids(K), price_list_line_ids(K),
2402 NULL, NULL, NULL, NULL, NULL, NULL,
2403 'GET_PROD_FLEX_PRC', err_msg, 'PRICE_LISTS');
2404 END;
2405 --l_pricing_attr_tbl(I) := l_pricing_attr_rec;
2406 */
2407
2408 end if;
2409
2410 --dbms_output.put_line('crpa 108');
2411
2412 If pricing_attribute8s(K) is not null then
2413 attr_count := attr_count + 1;
2414 attr_creation_dates(attr_count) := creation_dates(K);
2415 attr_created_bys(attr_count) := created_bys(K);
2416 attr_last_update_dates(attr_count) := last_update_dates(K);
2417 attr_last_updated_bys(attr_count) := last_updated_bys(K);
2418 attr_last_update_logins(attr_count) := last_update_logins(K);
2419 attr_program_application_ids(attr_count) := program_application_ids(K);
2420 attr_program_ids(attr_count) := program_ids(K);
2421 attr_program_update_dates(attr_count) := program_update_dates(K);
2422 attr_request_ids(attr_count) := request_ids(K);
2423 attr_list_line_ids(attr_count) := price_list_line_ids(K);
2424 attr_excluder_flags(attr_count) := 'N';
2425 attr_accumulate_flags(attr_count) := 'N';
2426 attr_product_attr_contexts(attr_count) := l_product_context;
2427 attr_product_attributes(attr_count) := l_product_attr;
2428 attr_product_attr_values(attr_count) := inventory_item_ids(K);
2429 attr_product_uom_codes(attr_count) := unit_codes(K);
2430 attr_comparison_operator_codes(attr_count) := '='; --3251389 --NULL;
2431 attr_pricing_contexts(attr_count) := l_pricing_context;
2432 attr_pricing_attrs(attr_count) := 'PRICING_ATTRIBUTE8';
2433 attr_pricing_attr_value_froms(attr_count) := pricing_attribute8s(K);
2434 attr_pricing_attr_value_tos(attr_count) := NULL;
2435 qp_util.get_segs_flex_precedence(p_segs_upg_t => v_segs_upg_t,
2436 p_context => attr_pricing_contexts(attr_count),
2437 p_attribute => attr_pricing_attrs(attr_count),
2438 x_precedence => l_prc_precedence,
2439 x_datatype => prc_datatypes(attr_count));
2440 /*
2441 BEGIN
2442 qp_util.get_prod_flex_properties
2443 ( attr_pricing_contexts(attr_count),
2444 attr_pricing_attrs(attr_count),
2445 attr_pricing_attr_value_froms(attr_count),
2446 prc_datatypes(attr_count), l_prc_precedence, l_error);
2447 IF l_error <> 0 THEN
2448 RAISE e_get_prod_flex_properties;
2449 END IF;
2450 EXCEPTION
2451 WHEN e_get_prod_flex_properties THEN
2452 err_num := SQLCODE;
2453 err_msg := SUBSTR(SQLERRM, 1, 240);
2454 qp_util.log_error(price_list_ids(K), price_list_line_ids(K),
2455 NULL, NULL, NULL, NULL, NULL, NULL,
2456 'GET_PROD_FLEX_PRC', err_msg, 'PRICE_LISTS');
2457 END;
2458 --l_pricing_attr_tbl(I) := l_pricing_attr_rec;
2459 */
2460
2461 end if;
2462
2463 --dbms_output.put_line('crpa 109');
2464
2465 If pricing_attribute9s(K) is not null then
2466 attr_count := attr_count + 1;
2467 attr_creation_dates(attr_count) := creation_dates(K);
2468 attr_created_bys(attr_count) := created_bys(K);
2469 attr_last_update_dates(attr_count) := last_update_dates(K);
2470 attr_last_updated_bys(attr_count) := last_updated_bys(K);
2471 attr_last_update_logins(attr_count) := last_update_logins(K);
2472 attr_program_application_ids(attr_count) := program_application_ids(K);
2473 attr_program_ids(attr_count) := program_ids(K);
2474 attr_program_update_dates(attr_count) := program_update_dates(K);
2475 attr_request_ids(attr_count) := request_ids(K);
2476 attr_list_line_ids(attr_count) := price_list_line_ids(K);
2477 attr_excluder_flags(attr_count) := 'N';
2478 attr_accumulate_flags(attr_count) := 'N';
2482 attr_product_uom_codes(attr_count) := unit_codes(K);
2479 attr_product_attr_contexts(attr_count) := l_product_context;
2480 attr_product_attributes(attr_count) := l_product_attr;
2481 attr_product_attr_values(attr_count) := inventory_item_ids(K);
2483 attr_comparison_operator_codes(attr_count) := '='; --3251389 --NULL;
2484 attr_pricing_contexts(attr_count) := l_pricing_context;
2485 attr_pricing_attrs(attr_count) := 'PRICING_ATTRIBUTE9';
2486 attr_pricing_attr_value_froms(attr_count) := pricing_attribute9s(K);
2487 attr_pricing_attr_value_tos(attr_count) := NULL;
2488 qp_util.get_segs_flex_precedence(p_segs_upg_t => v_segs_upg_t,
2489 p_context => attr_pricing_contexts(attr_count),
2490 p_attribute => attr_pricing_attrs(attr_count),
2491 x_precedence => l_prc_precedence,
2492 x_datatype => prc_datatypes(attr_count));
2493 /*
2494 BEGIN
2495 qp_util.get_prod_flex_properties
2496 ( attr_pricing_contexts(attr_count),
2497 attr_pricing_attrs(attr_count),
2498 attr_pricing_attr_value_froms(attr_count),
2499 prc_datatypes(attr_count), l_prc_precedence, l_error);
2500 IF l_error <> 0 THEN
2501 RAISE e_get_prod_flex_properties;
2502 END IF;
2503 EXCEPTION
2504 WHEN e_get_prod_flex_properties THEN
2505 err_num := SQLCODE;
2506 err_msg := SUBSTR(SQLERRM, 1, 240);
2507 qp_util.log_error(price_list_ids(K), price_list_line_ids(K),
2508 NULL, NULL, NULL, NULL, NULL, NULL,
2509 'GET_PROD_FLEX_PRC', err_msg, 'PRICE_LISTS');
2510 END;
2511 --l_pricing_attr_tbl(I) := l_pricing_attr_rec;
2512 */
2513
2514 end if;
2515
2516 --dbms_output.put_line('crpa 110');
2517
2518 If pricing_attribute10s(K) is not null then
2519 attr_count := attr_count + 1;
2520 attr_creation_dates(attr_count) := creation_dates(K);
2521 attr_created_bys(attr_count) := created_bys(K);
2522 attr_last_update_dates(attr_count) := last_update_dates(K);
2523 attr_last_updated_bys(attr_count) := last_updated_bys(K);
2524 attr_last_update_logins(attr_count) := last_update_logins(K);
2525 attr_program_application_ids(attr_count) := program_application_ids(K);
2526 attr_program_ids(attr_count) := program_ids(K);
2527 attr_program_update_dates(attr_count) := program_update_dates(K);
2528 attr_request_ids(attr_count) := request_ids(K);
2529 attr_list_line_ids(attr_count) := price_list_line_ids(K);
2530 attr_excluder_flags(attr_count) := 'N';
2531 attr_accumulate_flags(attr_count) := 'N';
2532 attr_product_attr_contexts(attr_count) := l_product_context;
2533 attr_product_attributes(attr_count) := l_product_attr;
2534 attr_product_attr_values(attr_count) := inventory_item_ids(K);
2535 attr_product_uom_codes(attr_count) := unit_codes(K);
2536 attr_comparison_operator_codes(attr_count) := '='; --3251389 --NULL;
2537 attr_pricing_contexts(attr_count) := l_pricing_context;
2538 attr_pricing_attrs(attr_count) := 'PRICING_ATTRIBUTE10';
2539 attr_pricing_attr_value_froms(attr_count) := pricing_attribute10s(K);
2540 attr_pricing_attr_value_tos(attr_count) := NULL;
2541 qp_util.get_segs_flex_precedence(p_segs_upg_t => v_segs_upg_t,
2542 p_context => attr_pricing_contexts(attr_count),
2543 p_attribute => attr_pricing_attrs(attr_count),
2544 x_precedence => l_prc_precedence,
2545 x_datatype => prc_datatypes(attr_count));
2546 /*
2547 BEGIN
2548 qp_util.get_prod_flex_properties
2549 ( attr_pricing_contexts(attr_count),
2550 attr_pricing_attrs(attr_count),
2551 attr_pricing_attr_value_froms(attr_count),
2552 prc_datatypes(attr_count), l_prc_precedence, l_error);
2553 IF l_error <> 0 THEN
2554 RAISE e_get_prod_flex_properties;
2555 END IF;
2556 EXCEPTION
2557 WHEN e_get_prod_flex_properties THEN
2558 err_num := SQLCODE;
2559 err_msg := SUBSTR(SQLERRM, 1, 240);
2560 qp_util.log_error(price_list_ids(K), price_list_line_ids(K),
2561 NULL, NULL, NULL, NULL, NULL, NULL,
2562 'GET_PROD_FLEX_PRC', err_msg, 'PRICE_LISTS');
2563 END;
2564 --l_pricing_attr_tbl(I) := l_pricing_attr_rec;
2565 */
2566
2567 end if;
2568
2569 --dbms_output.put_line('crpa 112');
2570
2571 If pricing_attribute11s(K) is not null then
2572 attr_count := attr_count + 1;
2573 attr_creation_dates(attr_count) := creation_dates(K);
2574 attr_created_bys(attr_count) := created_bys(K);
2575 attr_last_update_dates(attr_count) := last_update_dates(K);
2576 attr_last_updated_bys(attr_count) := last_updated_bys(K);
2577 attr_last_update_logins(attr_count) := last_update_logins(K);
2578 attr_program_application_ids(attr_count) := program_application_ids(K);
2579 attr_program_ids(attr_count) := program_ids(K);
2580 attr_program_update_dates(attr_count) := program_update_dates(K);
2581 attr_request_ids(attr_count) := request_ids(K);
2582 attr_list_line_ids(attr_count) := price_list_line_ids(K);
2583 attr_excluder_flags(attr_count) := 'N';
2584 attr_accumulate_flags(attr_count) := 'N';
2585 attr_product_attr_contexts(attr_count) := l_product_context;
2586 attr_product_attributes(attr_count) := l_product_attr;
2587 attr_product_attr_values(attr_count) := inventory_item_ids(K);
2591 attr_pricing_attrs(attr_count) := 'PRICING_ATTRIBUTE11';
2588 attr_product_uom_codes(attr_count) := unit_codes(K);
2589 attr_comparison_operator_codes(attr_count) := '='; --3251389 --NULL;
2590 attr_pricing_contexts(attr_count) := l_pricing_context;
2592 attr_pricing_attr_value_froms(attr_count) := pricing_attribute11s(K);
2593 attr_pricing_attr_value_tos(attr_count) := NULL;
2594 qp_util.get_segs_flex_precedence(p_segs_upg_t => v_segs_upg_t,
2595 p_context => attr_pricing_contexts(attr_count),
2596 p_attribute => attr_pricing_attrs(attr_count),
2597 x_precedence => l_prc_precedence,
2598 x_datatype => prc_datatypes(attr_count));
2599 /*
2600 BEGIN
2601 qp_util.get_prod_flex_properties
2602 ( attr_pricing_contexts(attr_count),
2603 attr_pricing_attrs(attr_count),
2604 attr_pricing_attr_value_froms(attr_count),
2605 prc_datatypes(attr_count), l_prc_precedence, l_error);
2606 IF l_error <> 0 THEN
2607 RAISE e_get_prod_flex_properties;
2608 END IF;
2609 EXCEPTION
2610 WHEN e_get_prod_flex_properties THEN
2611 err_num := SQLCODE;
2612 err_msg := SUBSTR(SQLERRM, 1, 240);
2613 qp_util.log_error(price_list_ids(K), price_list_line_ids(K),
2614 NULL, NULL, NULL, NULL, NULL, NULL,
2615 'GET_PROD_FLEX_PRC', err_msg, 'PRICE_LISTS');
2616 END;
2617 --l_pricing_attr_tbl(I) := l_pricing_attr_rec;
2618 */
2619
2620 end if;
2621
2622 --dbms_output.put_line('crpa 113');
2623
2624 If pricing_attribute12s(K) is not null then
2625 attr_count := attr_count + 1;
2626 attr_creation_dates(attr_count) := creation_dates(K);
2627 attr_created_bys(attr_count) := created_bys(K);
2628 attr_last_update_dates(attr_count) := last_update_dates(K);
2629 attr_last_updated_bys(attr_count) := last_updated_bys(K);
2630 attr_last_update_logins(attr_count) := last_update_logins(K);
2631 attr_program_application_ids(attr_count) := program_application_ids(K);
2632 attr_program_ids(attr_count) := program_ids(K);
2633 attr_program_update_dates(attr_count) := program_update_dates(K);
2634 attr_request_ids(attr_count) := request_ids(K);
2635 attr_list_line_ids(attr_count) := price_list_line_ids(K);
2636 attr_excluder_flags(attr_count) := 'N';
2637 attr_accumulate_flags(attr_count) := 'N';
2638 attr_product_attr_contexts(attr_count) := l_product_context;
2639 attr_product_attributes(attr_count) := l_product_attr;
2640 attr_product_attr_values(attr_count) := inventory_item_ids(K);
2641 attr_product_uom_codes(attr_count) := unit_codes(K);
2642 attr_comparison_operator_codes(attr_count) := '='; --3251389 --NULL;
2643 attr_pricing_contexts(attr_count) := l_pricing_context;
2644 attr_pricing_attrs(attr_count) := 'PRICING_ATTRIBUTE12';
2645 attr_pricing_attr_value_froms(attr_count) := pricing_attribute12s(K);
2646 attr_pricing_attr_value_tos(attr_count) := NULL;
2647 qp_util.get_segs_flex_precedence(p_segs_upg_t => v_segs_upg_t,
2648 p_context => attr_pricing_contexts(attr_count),
2649 p_attribute => attr_pricing_attrs(attr_count),
2650 x_precedence => l_prc_precedence,
2651 x_datatype => prc_datatypes(attr_count));
2652 /*
2653 BEGIN
2654 qp_util.get_prod_flex_properties
2655 ( attr_pricing_contexts(attr_count),
2656 attr_pricing_attrs(attr_count),
2657 attr_pricing_attr_value_froms(attr_count),
2658 prc_datatypes(attr_count), l_prc_precedence, l_error);
2659 IF l_error <> 0 THEN
2660 RAISE e_get_prod_flex_properties;
2661 END IF;
2662 EXCEPTION
2663 WHEN e_get_prod_flex_properties THEN
2664 err_num := SQLCODE;
2665 err_msg := SUBSTR(SQLERRM, 1, 240);
2666 qp_util.log_error(price_list_ids(K), price_list_line_ids(K),
2667 NULL, NULL, NULL, NULL, NULL, NULL,
2668 'GET_PROD_FLEX_PRC', err_msg, 'PRICE_LISTS');
2669 END;
2670 --l_pricing_attr_tbl(I) := l_pricing_attr_rec;
2671 */
2672
2673 end if;
2674
2675 If pricing_attribute13s(K) is not null then
2676 attr_count := attr_count + 1;
2677 attr_creation_dates(attr_count) := creation_dates(K);
2678 attr_created_bys(attr_count) := created_bys(K);
2679 attr_last_update_dates(attr_count) := last_update_dates(K);
2680 attr_last_updated_bys(attr_count) := last_updated_bys(K);
2681 attr_last_update_logins(attr_count) := last_update_logins(K);
2682 attr_program_application_ids(attr_count) := program_application_ids(K);
2683 attr_program_ids(attr_count) := program_ids(K);
2684 attr_program_update_dates(attr_count) := program_update_dates(K);
2685 attr_request_ids(attr_count) := request_ids(K);
2686 attr_list_line_ids(attr_count) := price_list_line_ids(K);
2687 attr_excluder_flags(attr_count) := 'N';
2688 attr_accumulate_flags(attr_count) := 'N';
2689 attr_product_attr_contexts(attr_count) := l_product_context;
2690 attr_product_attributes(attr_count) := l_product_attr;
2691 attr_product_attr_values(attr_count) := inventory_item_ids(K);
2692 attr_product_uom_codes(attr_count) := unit_codes(K);
2693 attr_comparison_operator_codes(attr_count) := '='; --3251389 --NULL;
2694 attr_pricing_contexts(attr_count) := l_pricing_context;
2695 attr_pricing_attrs(attr_count) := 'PRICING_ATTRIBUTE13';
2699 p_context => attr_pricing_contexts(attr_count),
2696 attr_pricing_attr_value_froms(attr_count) := pricing_attribute13s(K);
2697 attr_pricing_attr_value_tos(attr_count) := NULL;
2698 qp_util.get_segs_flex_precedence(p_segs_upg_t => v_segs_upg_t,
2700 p_attribute => attr_pricing_attrs(attr_count),
2701 x_precedence => l_prc_precedence,
2702 x_datatype => prc_datatypes(attr_count));
2703 /*
2704 BEGIN
2705 qp_util.get_prod_flex_properties
2706 ( attr_pricing_contexts(attr_count),
2707 attr_pricing_attrs(attr_count),
2708 attr_pricing_attr_value_froms(attr_count),
2709 prc_datatypes(attr_count), l_prc_precedence, l_error);
2710 IF l_error <> 0 THEN
2711 RAISE e_get_prod_flex_properties;
2712 END IF;
2713 EXCEPTION
2714 WHEN e_get_prod_flex_properties THEN
2715 err_num := SQLCODE;
2716 err_msg := SUBSTR(SQLERRM, 1, 240);
2717 qp_util.log_error(price_list_ids(K), price_list_line_ids(K),
2718 NULL, NULL, NULL, NULL, NULL, NULL,
2719 'GET_PROD_FLEX_PRC', err_msg, 'PRICE_LISTS');
2720 END;
2721 --l_pricing_attr_tbl(I) := l_pricing_attr_rec;
2722 */
2723
2724 end if;
2725
2726 If pricing_attribute14s(K) is not null then
2727 attr_count := attr_count + 1;
2728 attr_creation_dates(attr_count) := creation_dates(K);
2729 attr_created_bys(attr_count) := created_bys(K);
2730 attr_last_update_dates(attr_count) := last_update_dates(K);
2731 attr_last_updated_bys(attr_count) := last_updated_bys(K);
2732 attr_last_update_logins(attr_count) := last_update_logins(K);
2733 attr_program_application_ids(attr_count) := program_application_ids(K);
2734 attr_program_ids(attr_count) := program_ids(K);
2735 attr_program_update_dates(attr_count) := program_update_dates(K);
2736 attr_request_ids(attr_count) := request_ids(K);
2737 attr_list_line_ids(attr_count) := price_list_line_ids(K);
2738 attr_excluder_flags(attr_count) := 'N';
2739 attr_accumulate_flags(attr_count) := 'N';
2740 attr_product_attr_contexts(attr_count) := l_product_context;
2741 attr_product_attributes(attr_count) := l_product_attr;
2742 attr_product_attr_values(attr_count) := inventory_item_ids(K);
2743 attr_product_uom_codes(attr_count) := unit_codes(K);
2744 attr_comparison_operator_codes(attr_count) := '='; --3251389 --NULL;
2745 attr_pricing_contexts(attr_count) := l_pricing_context;
2746 attr_pricing_attrs(attr_count) := 'PRICING_ATTRIBUTE14';
2747 attr_pricing_attr_value_froms(attr_count) := pricing_attribute14s(K);
2748 attr_pricing_attr_value_tos(attr_count) := NULL;
2749 qp_util.get_segs_flex_precedence(p_segs_upg_t => v_segs_upg_t,
2750 p_context => attr_pricing_contexts(attr_count),
2751 p_attribute => attr_pricing_attrs(attr_count),
2752 x_precedence => l_prc_precedence,
2753 x_datatype => prc_datatypes(attr_count));
2754 /*
2755 BEGIN
2756 qp_util.get_prod_flex_properties
2757 ( attr_pricing_contexts(attr_count),
2758 attr_pricing_attrs(attr_count),
2759 attr_pricing_attr_value_froms(attr_count),
2760 prc_datatypes(attr_count), l_prc_precedence, l_error);
2761 IF l_error <> 0 THEN
2762 RAISE e_get_prod_flex_properties;
2763 END IF;
2764 EXCEPTION
2765 WHEN e_get_prod_flex_properties THEN
2766 err_num := SQLCODE;
2767 err_msg := SUBSTR(SQLERRM, 1, 240);
2768 qp_util.log_error(price_list_ids(K), price_list_line_ids(K),
2769 NULL, NULL, NULL, NULL, NULL, NULL,
2770 'GET_PROD_FLEX_PRC', err_msg, 'PRICE_LISTS');
2771 END;
2772 --l_pricing_attr_tbl(I) := l_pricing_attr_rec;
2773 */
2774
2775 end if;
2776
2777 --dbms_output.put_line('crpa 116');
2778
2779 If pricing_attribute15s(K) is not null then
2780 attr_count := attr_count + 1;
2781 attr_creation_dates(attr_count) := creation_dates(K);
2782 attr_created_bys(attr_count) := created_bys(K);
2783 attr_last_update_dates(attr_count) := last_update_dates(K);
2784 attr_last_updated_bys(attr_count) := last_updated_bys(K);
2785 attr_last_update_logins(attr_count) := last_update_logins(K);
2786 attr_program_application_ids(attr_count) := program_application_ids(K);
2787 attr_program_ids(attr_count) := program_ids(K);
2788 attr_program_update_dates(attr_count) := program_update_dates(K);
2789 attr_request_ids(attr_count) := request_ids(K);
2790 attr_list_line_ids(attr_count) := price_list_line_ids(K);
2791 attr_excluder_flags(attr_count) := 'N';
2792 attr_accumulate_flags(attr_count) := 'N';
2793 attr_product_attr_contexts(attr_count) := l_product_context;
2794 attr_product_attributes(attr_count) := l_product_attr;
2795 attr_product_attr_values(attr_count) := inventory_item_ids(K);
2796 attr_product_uom_codes(attr_count) := unit_codes(K);
2797 attr_comparison_operator_codes(attr_count) := '='; --3251389 --NULL;
2798 attr_pricing_contexts(attr_count) := l_pricing_context;
2799 attr_pricing_attrs(attr_count) := 'PRICING_ATTRIBUTE15';
2800 attr_pricing_attr_value_froms(attr_count) := pricing_attribute15s(K);
2801 attr_pricing_attr_value_tos(attr_count) := NULL;
2802 qp_util.get_segs_flex_precedence(p_segs_upg_t => v_segs_upg_t,
2806 x_datatype => prc_datatypes(attr_count));
2803 p_context => attr_pricing_contexts(attr_count),
2804 p_attribute => attr_pricing_attrs(attr_count),
2805 x_precedence => l_prc_precedence,
2807 /*
2808 BEGIN
2809 qp_util.get_prod_flex_properties
2810 ( attr_pricing_contexts(attr_count),
2811 attr_pricing_attrs(attr_count),
2812 attr_pricing_attr_value_froms(attr_count),
2813 prc_datatypes(attr_count), l_prc_precedence, l_error);
2814 IF l_error <> 0 THEN
2815 RAISE e_get_prod_flex_properties;
2816 END IF;
2817 EXCEPTION
2818 WHEN e_get_prod_flex_properties THEN
2819 err_num := SQLCODE;
2820 err_msg := SUBSTR(SQLERRM, 1, 240);
2821 qp_util.log_error(price_list_ids(K), price_list_line_ids(K),
2822 NULL, NULL, NULL, NULL, NULL, NULL,
2823 'GET_PROD_FLEX_PRC', err_msg, 'PRICE_LISTS');
2824 END;
2825 --l_pricing_attr_tbl(I) := l_pricing_attr_rec;
2826 */
2827 end if;
2828
2829 END LOOP;
2830
2831 --dbms_output.put_line('crpa 117');
2832 --qp_util.log_error(NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2833 -- NULL, 'MAIN-6', v_errortext, 'PRICE_LISTS');
2834
2835 BEGIN
2836
2837 FORALL J IN 1..attr_count
2838 INSERT INTO QP_PRICING_ATTRIBUTES
2839 (PRICING_ATTRIBUTE_ID,
2840 CREATION_DATE,
2841 CREATED_BY,
2842 LAST_UPDATE_DATE,
2843 LAST_UPDATED_BY,
2844 LAST_UPDATE_LOGIN,
2845 PROGRAM_APPLICATION_ID,
2846 PROGRAM_ID,
2847 PROGRAM_UPDATE_DATE,
2848 REQUEST_ID,
2849 LIST_LINE_ID,
2850 EXCLUDER_FLAG,
2851 ACCUMULATE_FLAG,
2852 PRODUCT_ATTRIBUTE_CONTEXT,
2853 PRODUCT_ATTRIBUTE,
2854 PRODUCT_ATTR_VALUE,
2855 PRODUCT_UOM_CODE,
2856 PRICING_ATTRIBUTE_CONTEXT,
2857 PRICING_ATTRIBUTE,
2858 PRICING_ATTR_VALUE_FROM,
2859 PRICING_ATTR_VALUE_TO,
2860 ATTRIBUTE_GROUPING_NO,
2861 COMPARISON_OPERATOR_CODE,
2862 PRICING_ATTRIBUTE_DATATYPE,
2863 PRODUCT_ATTRIBUTE_DATATYPE
2864 --ENH Upgrade BOAPI for orig_sys...ref RAVI
2865 ,ORIG_SYS_PRICING_ATTR_REF
2866 ,ORIG_SYS_LINE_REF
2867 ,ORIG_SYS_HEADER_REF)
2868 VALUES
2869 (
2870 QP_PRICING_ATTRIBUTES_S.nextval,
2871 attr_creation_dates(J),
2872 attr_created_bys(J),
2873 attr_last_update_dates(J),
2874 attr_last_updated_bys(J),
2875 attr_last_update_logins(J),
2876 attr_program_application_ids(J),
2877 attr_program_ids(J),
2878 attr_program_update_dates(J),
2879 attr_request_ids(J),
2880 attr_list_line_ids(J),
2881 attr_excluder_flags(J),
2882 attr_accumulate_flags(J),
2883 attr_product_attr_contexts(J),
2884 attr_product_attributes(J),
2885 attr_product_attr_values(J),
2886 attr_product_uom_codes(J),
2887 attr_pricing_contexts(J),
2888 attr_pricing_attrs(J),
2889 attr_pricing_attr_value_froms(J),
2890 attr_pricing_attr_value_tos(J),
2891 1 ,
2892 attr_comparison_operator_codes(J),
2893 prc_datatypes(J),
2894 l_prod_datatype
2895 --ENH Upgrade BOAPI for orig_sys...ref RAVI
2896 ,to_char(QP_PRICING_ATTRIBUTES_S.currval)
2897 ,(select l.ORIG_SYS_LINE_REF from qp_list_lines l where l.list_line_id=attr_list_line_ids(J))
2898 ,(select l.ORIG_SYS_HEADER_REF from qp_list_lines l where l.list_line_id=attr_list_line_ids(J))
2899 );
2900
2901
2902 EXCEPTION
2903 WHEN OTHERS THEN
2904 err_msg := SQLERRM;
2905 J := sql%rowcount + 1;
2906 rollback;
2907 QP_Util.Log_Error(p_id1 => attr_list_line_ids(J) ,
2908 p_id2 => NULL,
2909 p_error_type => 'PRICE_LIST_LINES_ATTR',
2910 p_error_desc => err_msg,
2911 p_error_module => 'Create_Pricing_Attribute');
2912 raise;
2913
2914 END;
2915
2916 --qp_util.log_error(NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2917 -- NULL, 'MAIN-7', v_errortext, 'PRICE_LISTS');
2918
2919
2920
2921 /* insert into qp_discount_mapping table for the rows that were
2922 just now inserted */
2923
2924 -- qp_util.log_error(NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2925 -- NULL, 'MAIN-8', v_errortext, 'PRICE_LISTS');
2926
2927 /*
2928
2929 FORALL l_prc_list_map_index IN new_prc_list_maps.FIRST..new_prc_list_maps.LAST
2930 INSERT INTO QP_DISCOUNT_MAPPING(OLD_DISCOUNT_ID,
2931 OLD_DISCOUNT_LINE_ID,
2932 NEW_LIST_HEADER_ID,
2933 NEW_LIST_LINE_ID,
2934 OLD_PRICE_BREAK_LINES_LOW,
2935 OLD_PRICE_BREAK_LINES_HIGH,
2936 OLD_METHOD_TYPE_CODE,
2937 OLD_PRICE_BREAK_PERCENT,
2938 OLD_PRICE_BREAK_AMOUNT,
2939 OLD_PRICE_BREAK_PRICE,
2940 NEW_TYPE,
2941 PRICING_CONTEXT)
2942 VALUES (new_prc_list_maps(l_prc_list_map_index).old_price_list_id,
2943 new_prc_list_maps(l_prc_list_map_index).old_price_list_line_id,
2944 new_prc_list_maps(l_prc_list_map_index).new_list_header_id,
2945 new_prc_list_maps(l_prc_list_map_index).new_list_line_id,
2946 NULL,
2947 NULL,
2948 NULL,
2949 NULL,
2950 NULL,
2951 NULL,
2952 'P',
2953 NULL);
2954
2955 */
2956
2957 -- qp_util.log_error(NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2958 -- NULL, 'MAIN-9', v_errortext, 'PRICE_LISTS');
2959 /* vivek
2960
2961 prc_list_maps := new_prc_list_maps;
2962
2963 vivek */
2964
2965
2966 commit;
2967
2968 /* vivek prc_list_maps.delete; vivek */
2969
2970 END IF; /* IF PRICE_LIST_LINE_IDS.FIRST IS NOT NULL */
2971
2972 END LOOP; /* while loop */
2973
2974 COMMIT;
2975
2976 exception
2977
2978 when others then
2979 v_errortext := SUBSTR(SQLERRM, 1,240);
2980 K := sql%rowcount + l_min_line;
2981 ROLLBACK;
2982 If price_list_ids.exists(k) then
2983 qp_util.log_error(price_list_ids(K), NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'PRICE_LIST_LINES_MAIN', v_errortext, 'PRICE_LISTS');
2984 else
2985 qp_util.log_error(-1111, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'PRICE_LIST_LINES_MAIN', v_errortext, 'PRICE_LISTS');
2986 end if;
2987 RAISE;
2988
2989 end create_list_lines;
2990
2991
2992 end qp_list_upgrade_util_pvt;