[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 = p_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
257: g_pattern_upg_slab_table.delete;
258: IF p_list_header_id IS NULL THEN
259: select count(*)
260: into l_total_lines
261: from qp_list_lines
262: where qualification_ind in (8,10,12,14,28,30, 4,6,20,22);
263: ELSE
264: select count(*)
265: into l_total_lines
262: where qualification_ind in (8,10,12,14,28,30, 4,6,20,22);
263: ELSE
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: and list_header_id = p_list_header_id;
269: END IF;
270: fnd_file.put_line(FND_FILE.LOG, 'l_total_lines ' || l_total_lines);
308:
309: else -- if l_total_lines > 0
310: if p_list_header_id is null then
311: -- like upgrade, refresh everything related to patterns
312: update qp_list_lines
313: set pattern_id = null,
314: pricing_attribute_count = null,
315: product_uom_code = null,
316: hash_key = null,
323: delete from qp_attribute_groups
324: where list_header_id = p_list_header_id
325: and list_line_id <> -1;
326:
327: update qp_list_lines
328: set pattern_id = null,
329: pricing_attribute_count = null,
330: product_uom_code = null,
331: hash_key = null,
341: select hsecs into l_start_time from v$timer;
342: fnd_file.put_line(FND_FILE.LOG, 'Start time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
343: if p_list_header_id is null then
344: -- like upgrade, refresh everything related to patterns
345: update /*+ index_asc(lines QP_LIST_LINES_PK) */ qp_list_lines lines
346: set pattern_id = null,
347: pricing_attribute_count = null,
348: product_uom_code = null,
349: hash_key = null,
356: delete from qp_attribute_groups
357: where list_header_id = p_list_header_id
358: and list_line_id between p_low_list_line_id and p_high_list_line_id;
359:
360: update /*+ index_asc(lines QP_LIST_LINES_PK) */ qp_list_lines lines
361: set pattern_id = null,
362: pricing_attribute_count = null,
363: product_uom_code = null,
364: hash_key = null,
508: ,p_high_list_line_id IN NUMBER)
509: is
510: cursor c_attr_grp_lq_csr is
511: select * from
512: (select /*+ ordered use_nl(qpq, qph) index(qpl qp_list_lines_pk) */ qpq.list_header_id,
513: qpq.list_line_id,
514: qpq.segment_id,
515: qpq.active_flag,
516: qpq.list_type_code,
527: qpl.modifier_level_code modifier_level_code,
528: qpq.qualifier_datatype attribute_datatype,
529: qpq.qualifier_attr_value attribute_value,
530: 'QUAL' attribute_type
531: from qp_list_lines qpl, qp_qualifiers qpq, qp_list_headers_b qph
532: where qpq.list_line_id <> -1
533: and qph.list_header_id = qpq.list_header_id
534: and qpl.list_header_id = qph.list_header_id
535: and qpl.list_line_id = qpq.list_line_id
540: OR
541: (p_qualifier_group is null)
542: )
543: union
544: select /*+ ordered use_nl(qpa, qph) index(qpl qp_list_lines_pk) */ distinct qpl.list_header_id,
545: qpl.list_line_id,
546: qpa.product_segment_id segment_id,
547: qph.active_flag,
548: qph.list_type_code,
559: qpl.modifier_level_code modifier_level_code,
560: qpa.product_attribute_datatype attribute_datatype,
561: qpa.product_attr_value attribute_value,
562: 'PROD' attribute_type
563: from qp_list_lines qpl, qp_pricing_attributes qpa, qp_list_headers_b qph
564: where qph.list_header_id = qpl.list_header_id
565: and qpl.list_line_id = qpa.list_line_id
566: and qpa.excluder_flag = 'N'
567: and qpl.pricing_phase_id > 1
571: qpa.pricing_attribute_context is null
572: )
573: and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
574: union
575: select /*+ ordered use_nl(qpa, qph) index(qpl qp_list_lines_pk) */ qpl.list_header_id,
576: qpl.list_line_id,
577: qpa.pricing_segment_id segment_id,
578: qph.active_flag,
579: qph.list_type_code,
590: qpl.modifier_level_code modifier_level_code,
591: qpa.pricing_attribute_datatype attribute_datatype,
592: qpa.pricing_attr_value_from attribute_value,
593: 'PRIC' attribute_type
594: from qp_list_lines qpl, qp_pricing_attributes qpa, qp_list_headers_b qph
595: where qph.list_header_id = qpl.list_header_id
596: and qpl.list_line_id = qpa.list_line_id
597: and qpl.pricing_phase_id > 1
598: and qpl.qualification_ind in (8,10,12,14,28,30)
651: ,p_high_list_line_id IN NUMBER)
652: is
653: cursor c_lines_pp_csr is
654: select * from
655: (select /*+ ordered use_nl(qpa) index(qpl qp_list_lines_pk) */ distinct qpa.list_header_id,
656: qpa.list_line_id,
657: qpa.product_segment_id segment_id,
658: '=' comparison_operator_code,
659: qpa.pricing_phase_id,
660: qpa.product_uom_code,
661: qpa.product_attribute_datatype attribute_datatype,
662: qpa.product_attr_value attribute_value,
663: 'PROD' attribute_type
664: from qp_list_lines qpl,
665: qp_pricing_attributes qpa
666: where qpl.list_line_id = qpa.list_line_id
667: and qpa.excluder_flag = 'N'
668: and qpl.qualification_ind in (4,6,20,22)
671: qpa.pricing_attribute_context is null
672: )
673: and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
674: union
675: select /*+ ordered use_nl(qpa) index(qpl qp_list_lines_pk) */ qpa.list_header_id,
676: qpa.list_line_id,
677: qpa.pricing_segment_id segment_id,
678: qpa.comparison_operator_code,
679: qpa.pricing_phase_id,
680: qpa.product_uom_code,
681: qpa.pricing_attribute_datatype attribute_datatype,
682: qpa.pricing_attr_value_from attribute_value,
683: 'PRIC' attribute_type
684: from qp_list_lines qpl,
685: qp_pricing_attributes qpa
686: where qpl.list_line_id = qpa.list_line_id
687: and qpl.qualification_ind in (20,22)
688: and qpa.pricing_attribute_context is not null
1143:
1144: -- insert into qp_attribute_groups from final tables
1145: populate_atgrps;
1146:
1147: -- for line pattern, qp_list_lines.cache_key need to be populated as well
1148: if p_pattern_type = 'LP' then
1149: update_list_lines_cache_key;
1150: end if;
1151:
1320:
1321: -- move data for last pair of header and line id
1322: Move_data_from_tmp_to_final(p_pattern_type);
1323:
1324: -- update qp_list_lines from final tables
1325: update_list_lines;
1326:
1327: EXCEPTION
1328: WHEN OTHERS THEN
1434: g_cache_key_final_tbl(l_atgrp_final_index) := g_list_header_id_tmp_tbl(grp_no_index);
1435:
1436: -- bug 3703391 - if product not given, even then consider product precedence to decide effective precedence
1437: select product_precedence into l_product_precedence
1438: from qp_list_lines where list_line_id = g_list_line_id_final_tbl(l_atgrp_final_index);
1439:
1440: if (l_product_precedence is not null) and (g_effec_precedence_final_tbl(l_atgrp_final_index) is not null)
1441: and (l_product_precedence < g_effec_precedence_final_tbl(l_atgrp_final_index)) then
1442: g_effec_precedence_final_tbl(l_atgrp_final_index) := l_product_precedence;
1574: end if;
1575: raise;
1576: END Populate_Patterns;
1577:
1578: -- bulk update qp_list_lines table
1579: PROCEDURE update_list_lines
1580: is
1581: BEGIN
1582:
1580: is
1581: BEGIN
1582:
1583: FORALL i in 1 .. g_list_line_id_final_tbl.count
1584: UPDATE /*+ index(lines QP_LIST_LINES_PK) */ qp_list_lines lines
1585: set pattern_id = g_pattern_id_final_tbl(i),
1586: product_uom_code = g_product_uom_code_final_tbl(i),
1587: pricing_attribute_count = g_pricing_attr_count_final_tbl(i),
1588: hash_key = g_hash_key_final_tbl(i),
1601: end if;
1602:
1603: end update_list_lines;
1604:
1605: -- bulk update qp_list_lines.cache_key for line patterns
1606: PROCEDURE update_list_lines_cache_key
1607: is
1608: BEGIN
1609:
1607: is
1608: BEGIN
1609:
1610: FORALL i in 1 .. g_list_line_id_final_tbl.count
1611: UPDATE qp_list_lines
1612: set cache_key = g_cache_key_final_tbl(i),
1613: last_update_date = g_last_update_date_final_tbl(i),
1614: last_updated_by = g_last_updated_by_final_tbl(i),
1615: last_update_login = g_last_update_login_final_tbl(i)
2228: l_status_code VARCHAR2(30) := NULL;
2229: l_status_text VARCHAR2(2000) := NULL;
2230: l_pid NUMBER := NULL;
2231: l_qual_exists varchar2(1) := 'N';
2232: l_line_LIMIT_EXISTS qp_list_lines.LIMIT_EXISTS_FLAG%type;
2233:
2234: BEGIN
2235: g_call_from_setup := 'Y';
2236:
2275: p_list_line_id, p_list_line_id);
2276: elsif p_setup_action = 'U' or p_setup_action = 'D' then
2277: -- update or delete case
2278: if p_qualifier_group is null then
2279: update qp_list_lines
2280: set pattern_id = null,
2281: hash_key = null,
2282: cache_key = null
2283: where list_line_id = p_list_line_id
2299: end;
2300:
2301: if l_qual_exists = 'Y' then
2302: -- means some qualifiers still exist for p_qualifier_group
2303: update qp_list_lines
2304: set pattern_id = null,
2305: hash_key = null,
2306: cache_key = null
2307: where list_line_id = p_list_line_id
2325: if l_qual_exists = 'N' then
2326: -- no qualifiers exist other than -1 qualifier_grouping_no
2327: -- this may insert in qp_attribute_groups with LINE_QUAL_GROUPING_NO = -1, if any
2328: -- qualifiers exist with qualifier_grouping_no = -1
2329: update qp_list_lines
2330: set pattern_id = null,
2331: hash_key = null,
2332: cache_key = null
2333: where list_line_id = p_list_line_id
2340: elsif p_setup_action = 'UD' then
2341: begin
2342: select LIMIT_EXISTS_FLAG
2343: into l_line_LIMIT_EXISTS
2344: from qp_list_lines
2345: where list_line_id = p_list_line_id;
2346:
2347: -- update line pattern records
2348: update qp_attribute_groups
2384: l_qual_exists := 'Y';
2385: end if;
2386:
2387: if p_setup_action = 'I' then
2388: -- assume LP exists, and so nullify the PP values in qp_list_lines table
2389: --except cache_key
2390: update qp_list_lines
2391: set pattern_id = null,
2392: pricing_attribute_count = null,
2386:
2387: if p_setup_action = 'I' then
2388: -- assume LP exists, and so nullify the PP values in qp_list_lines table
2389: --except cache_key
2390: update qp_list_lines
2391: set pattern_id = null,
2392: pricing_attribute_count = null,
2393: product_uom_code = null,
2394: hash_key = null
2420:
2421: l_status_code VARCHAR2(30) := NULL;
2422: l_status_text VARCHAR2(2000) := NULL;
2423: l_qual_exists varchar2(1) := 'N';
2424: l_product_uom_code qp_list_lines.product_uom_code%type;
2425: l_qual_ind number;
2426:
2427: BEGIN
2428: g_call_from_setup := 'Y';
2438: end if;
2439:
2440: select qualification_ind
2441: into l_qual_ind
2442: from qp_list_lines
2443: where list_line_id = p_list_line_id;
2444:
2445: oe_debug_pub.add('Product_Pattern_Main - l_qual_ind = ' ||l_qual_ind);
2446:
2451: return;
2452: end if;
2453:
2454: if p_setup_action = 'UD' then
2455: -- update qp_list_lines.product_uom_code
2456: begin
2457: select product_uom_code
2458: into l_product_uom_code
2459: from qp_pricing_attributes
2465: when no_data_found then
2466: l_product_uom_code := null;
2467: end;
2468:
2469: update qp_list_lines
2470: set product_uom_code = l_product_uom_code
2471: where list_line_id = p_list_line_id;
2472: else
2473: -- when called while set up of price list/modifier
2519:
2520: procedure Remove_Prod_Pattern_for_Line(p_list_line_id IN NUMBER)
2521: is
2522: begin
2523: update qp_list_lines
2524: set pattern_id = null,
2525: pricing_attribute_count = null,
2526: product_uom_code = null,
2527: hash_key = null,