[Home] [Help]
8: p_list_header_id IN number default null,
9: p_no_of_threads IN NUMBER default 1)
10: is
11: cursor list_line is
12: select list_line_id from qp_list_lines where qualification_ind in (8,10,12,14,28,30, 4,6,20,22)
13: and list_header_id = nvl(p_list_header_id, list_header_id)
14: order by list_line_id;
15:
16: l_gap number := 0;
125: where c.pattern_type = g_pattern_pat_type_final_tbl(i)
126: and c.pattern_string = g_pattern_pat_string_final_tbl(i);
127:
128: if g_pattern_pat_type_final_tbl(i) = 'PP' then
129: update /*+ index(lines QP_LIST_LINES_N9) */ qp_list_lines lines
130: set pattern_id = l_min_pattern_id
131: where pattern_id = g_pattern_pattern_id_final_tbl(i);
132: else
133: update qp_attribute_groups
262: g_pattern_upg_slab_table.delete;
263: IF p_list_header_id IS NULL THEN
264: select count(*)
265: into l_total_lines
266: from qp_list_lines
267: where qualification_ind in (8,10,12,14,28,30, 4,6,20,22);
268: ELSE
269: select count(*)
270: into l_total_lines
267: where qualification_ind in (8,10,12,14,28,30, 4,6,20,22);
268: ELSE
269: select count(*)
270: into l_total_lines
271: from qp_list_lines
272: where qualification_ind in (8,10,12,14,28,30, 4,6,20,22)
273: and list_header_id = p_list_header_id;
274: END IF;
275: fnd_file.put_line(FND_FILE.LOG, 'l_total_lines ' || l_total_lines);
314:
315: else -- if l_total_lines > 0
316: if p_list_header_id is null then
317: -- like upgrade, refresh everything related to patterns
318: update qp_list_lines
319: set pattern_id = null,
320: pricing_attribute_count = null,
321: product_uom_code = null,
322: hash_key = null,
329: delete from qp_attribute_groups
330: where list_header_id = p_list_header_id
331: and list_line_id <> -1;
332:
333: update qp_list_lines
334: set pattern_id = null,
335: pricing_attribute_count = null,
336: product_uom_code = null,
337: hash_key = null,
347: select hsecs into l_start_time from v$timer;
348: fnd_file.put_line(FND_FILE.LOG, 'Start time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
349: if p_list_header_id is null then
350: -- like upgrade, refresh everything related to patterns
351: update /*+ index_asc(lines QP_LIST_LINES_PK) */ qp_list_lines lines
352: set pattern_id = null,
353: pricing_attribute_count = null,
354: product_uom_code = null,
355: hash_key = null,
362: delete from qp_attribute_groups
363: where list_header_id = p_list_header_id
364: and list_line_id between p_low_list_line_id and p_high_list_line_id;
365:
366: update /*+ index_asc(lines QP_LIST_LINES_PK) */ qp_list_lines lines
367: set pattern_id = null,
368: pricing_attribute_count = null,
369: product_uom_code = null,
370: hash_key = null,
514: ,p_high_list_line_id IN NUMBER)
515: is
516: cursor c_attr_grp_lq_csr is
517: select * from
518: (select /*+ ordered use_nl(qpq, qph) index(qpl QP_LIST_LINES_N6) */ qpq.list_header_id,
519: qpq.list_line_id,
520: qpq.segment_id,
521: qpq.active_flag,
522: qpq.list_type_code,
533: qpl.modifier_level_code modifier_level_code,
534: qpq.qualifier_datatype attribute_datatype,
535: qpq.qualifier_attr_value attribute_value,
536: 'QUAL' attribute_type
537: from qp_list_lines qpl, qp_qualifiers qpq, qp_list_headers_b qph
538: where qpq.list_line_id <> -1
539: and qph.list_header_id = qpq.list_header_id
540: and qpl.list_header_id = qph.list_header_id
541: and qpl.list_line_id = qpq.list_line_id
546: OR
547: (p_qualifier_group is null)
548: )
549: union
550: select /*+ ordered use_nl(qpa, qph) index(qpl QP_LIST_LINES_N6) */ distinct qpl.list_header_id,
551: qpl.list_line_id,
552: qpa.product_segment_id segment_id,
553: qph.active_flag,
554: qph.list_type_code,
565: qpl.modifier_level_code modifier_level_code,
566: qpa.product_attribute_datatype attribute_datatype,
567: qpa.product_attr_value attribute_value,
568: 'PROD' attribute_type
569: from qp_list_lines qpl, qp_pricing_attributes qpa, qp_list_headers_b qph
570: where qph.list_header_id = qpl.list_header_id
571: and qpl.list_line_id = qpa.list_line_id
572: and qpa.excluder_flag = 'N'
573: and qpl.pricing_phase_id > 1
577: qpa.pricing_attribute_context is null
578: )
579: and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
580: union
581: select /*+ ordered use_nl(qpa, qph) index(qpl QP_LIST_LINES_N6) */ qpl.list_header_id,
582: qpl.list_line_id,
583: qpa.pricing_segment_id segment_id,
584: qph.active_flag,
585: qph.list_type_code,
596: qpl.modifier_level_code modifier_level_code,
597: qpa.pricing_attribute_datatype attribute_datatype,
598: qpa.pricing_attr_value_from attribute_value,
599: 'PRIC' attribute_type
600: from qp_list_lines qpl, qp_pricing_attributes qpa, qp_list_headers_b qph
601: where qph.list_header_id = qpl.list_header_id
602: and qpl.list_line_id = qpa.list_line_id
603: and qpl.pricing_phase_id > 1
604: and qpl.qualification_ind in (8,10,12,14,28,30)
657: ,p_high_list_line_id IN NUMBER)
658: is
659: cursor c_lines_pp_csr is
660: select * from
661: (select /*+ ordered use_nl(qpa) index(qpl QP_LIST_LINES_N6) */ distinct qpa.list_header_id,
662: qpa.list_line_id,
663: qpa.product_segment_id segment_id,
664: '=' comparison_operator_code,
665: qpa.pricing_phase_id,
666: qpa.product_uom_code,
667: qpa.product_attribute_datatype attribute_datatype,
668: qpa.product_attr_value attribute_value,
669: 'PROD' attribute_type
670: from qp_list_lines qpl,
671: qp_pricing_attributes qpa
672: where qpl.list_line_id = qpa.list_line_id
673: and qpa.excluder_flag = 'N'
674: and qpl.qualification_ind in (4,6,20,22)
677: qpa.pricing_attribute_context is null
678: )
679: and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
680: union
681: select /*+ ordered use_nl(qpa) index(qpl QP_LIST_LINES_N6) */ qpa.list_header_id,
682: qpa.list_line_id,
683: qpa.pricing_segment_id segment_id,
684: qpa.comparison_operator_code,
685: qpa.pricing_phase_id,
686: qpa.product_uom_code,
687: qpa.pricing_attribute_datatype attribute_datatype,
688: qpa.pricing_attr_value_from attribute_value,
689: 'PRIC' attribute_type
690: from qp_list_lines qpl,
691: qp_pricing_attributes qpa
692: where qpl.list_line_id = qpa.list_line_id
693: and qpl.qualification_ind in (20,22)
694: and qpa.pricing_attribute_context is not null
1149:
1150: -- insert into qp_attribute_groups from final tables
1151: populate_atgrps;
1152:
1153: -- for line pattern, qp_list_lines.cache_key need to be populated as well
1154: if p_pattern_type = 'LP' then
1155: update_list_lines_cache_key;
1156: end if;
1157:
1326:
1327: -- move data for last pair of header and line id
1328: Move_data_from_tmp_to_final(p_pattern_type);
1329:
1330: -- update qp_list_lines from final tables
1331: update_list_lines;
1332:
1333: EXCEPTION
1334: WHEN OTHERS THEN
1440: g_cache_key_final_tbl(l_atgrp_final_index) := g_list_header_id_tmp_tbl(grp_no_index);
1441:
1442: -- bug 3703391 - if product not given, even then consider product precedence to decide effective precedence
1443: select product_precedence into l_product_precedence
1444: from qp_list_lines where list_line_id = g_list_line_id_final_tbl(l_atgrp_final_index);
1445:
1446: if (l_product_precedence is not null) and (g_effec_precedence_final_tbl(l_atgrp_final_index) is not null)
1447: and (l_product_precedence < g_effec_precedence_final_tbl(l_atgrp_final_index)) then
1448: g_effec_precedence_final_tbl(l_atgrp_final_index) := l_product_precedence;
1580: end if;
1581: raise;
1582: END Populate_Patterns;
1583:
1584: -- bulk update qp_list_lines table
1585: PROCEDURE update_list_lines
1586: is
1587: BEGIN
1588:
1586: is
1587: BEGIN
1588:
1589: FORALL i in 1 .. g_list_line_id_final_tbl.count
1590: UPDATE /*+ index(lines QP_LIST_LINES_PK) */ qp_list_lines lines
1591: set pattern_id = g_pattern_id_final_tbl(i),
1592: product_uom_code = g_product_uom_code_final_tbl(i),
1593: pricing_attribute_count = g_pricing_attr_count_final_tbl(i),
1594: hash_key = g_hash_key_final_tbl(i),
1607: end if;
1608:
1609: end update_list_lines;
1610:
1611: -- bulk update qp_list_lines.cache_key for line patterns
1612: PROCEDURE update_list_lines_cache_key
1613: is
1614: BEGIN
1615:
1613: is
1614: BEGIN
1615:
1616: FORALL i in 1 .. g_list_line_id_final_tbl.count
1617: UPDATE qp_list_lines
1618: set cache_key = g_cache_key_final_tbl(i),
1619: last_update_date = g_last_update_date_final_tbl(i),
1620: last_updated_by = g_last_updated_by_final_tbl(i),
1621: last_update_login = g_last_update_login_final_tbl(i)
2234: l_status_code VARCHAR2(30) := NULL;
2235: l_status_text VARCHAR2(2000) := NULL;
2236: l_pid NUMBER := NULL;
2237: l_qual_exists varchar2(1) := 'N';
2238: l_line_LIMIT_EXISTS qp_list_lines.LIMIT_EXISTS_FLAG%type;
2239:
2240: BEGIN
2241: g_call_from_setup := 'Y';
2242:
2281: p_list_line_id, p_list_line_id);
2282: elsif p_setup_action = 'U' or p_setup_action = 'D' then
2283: -- update or delete case
2284: if p_qualifier_group is null then
2285: update qp_list_lines
2286: set pattern_id = null,
2287: hash_key = null,
2288: cache_key = null
2289: where list_line_id = p_list_line_id
2305: end;
2306:
2307: if l_qual_exists = 'Y' then
2308: -- means some qualifiers still exist for p_qualifier_group
2309: update qp_list_lines
2310: set pattern_id = null,
2311: hash_key = null,
2312: cache_key = null
2313: where list_line_id = p_list_line_id
2331: if l_qual_exists = 'N' then
2332: -- no qualifiers exist other than -1 qualifier_grouping_no
2333: -- this may insert in qp_attribute_groups with LINE_QUAL_GROUPING_NO = -1, if any
2334: -- qualifiers exist with qualifier_grouping_no = -1
2335: update qp_list_lines
2336: set pattern_id = null,
2337: hash_key = null,
2338: cache_key = null
2339: where list_line_id = p_list_line_id
2346: elsif p_setup_action = 'UD' then
2347: begin
2348: select LIMIT_EXISTS_FLAG
2349: into l_line_LIMIT_EXISTS
2350: from qp_list_lines
2351: where list_line_id = p_list_line_id;
2352:
2353: -- update line pattern records
2354: update qp_attribute_groups
2390: l_qual_exists := 'Y';
2391: end if;
2392:
2393: if p_setup_action = 'I' then
2394: -- assume LP exists, and so nullify the PP values in qp_list_lines table
2395: --except cache_key
2396: update qp_list_lines
2397: set pattern_id = null,
2398: pricing_attribute_count = null,
2392:
2393: if p_setup_action = 'I' then
2394: -- assume LP exists, and so nullify the PP values in qp_list_lines table
2395: --except cache_key
2396: update qp_list_lines
2397: set pattern_id = null,
2398: pricing_attribute_count = null,
2399: product_uom_code = null,
2400: hash_key = null
2426:
2427: l_status_code VARCHAR2(30) := NULL;
2428: l_status_text VARCHAR2(2000) := NULL;
2429: l_qual_exists varchar2(1) := 'N';
2430: l_product_uom_code qp_list_lines.product_uom_code%type;
2431: l_qual_ind number;
2432:
2433: BEGIN
2434: g_call_from_setup := 'Y';
2444: end if;
2445:
2446: select qualification_ind
2447: into l_qual_ind
2448: from qp_list_lines
2449: where list_line_id = p_list_line_id;
2450:
2451: oe_debug_pub.add('Product_Pattern_Main - l_qual_ind = ' ||l_qual_ind);
2452:
2457: return;
2458: end if;
2459:
2460: if p_setup_action = 'UD' then
2461: -- update qp_list_lines.product_uom_code
2462: begin
2463: select product_uom_code
2464: into l_product_uom_code
2465: from qp_pricing_attributes
2471: when no_data_found then
2472: l_product_uom_code := null;
2473: end;
2474:
2475: update qp_list_lines
2476: set product_uom_code = l_product_uom_code
2477: where list_line_id = p_list_line_id;
2478: else
2479: -- when called while set up of price list/modifier
2525:
2526: procedure Remove_Prod_Pattern_for_Line(p_list_line_id IN NUMBER)
2527: is
2528: begin
2529: update qp_list_lines
2530: set pattern_id = null,
2531: pricing_attribute_count = null,
2532: product_uom_code = null,
2533: hash_key = null,