[Home] [Help]
35: -- Price List Lines
36: SELECT /*+ ordered USE_NL(qpl qph) */
37: qpl.pricing_phase_id, qpl.pattern_id, qpl.START_DATE_ACTIVE sda,
38: qpl.END_DATE_ACTIVE eda, qph.active_flag
39: FROM qp_list_lines qpl, qp_list_headers_all_b qph
40: WHERE pricing_phase_id = 1
41: AND qph.list_header_id = qpl.list_header_id
42: UNION ALL
43: -- Price List headers
81: NVL(MAX(active_flag),'N') active_flag
82: FROM
83: (
84: -- Price List Lines
85: SELECT /*+ ordered USE_NL(qpl qph) index(qpl QP_LIST_LINES_N1) */
86: qpl.pricing_phase_id, qpl.pattern_id, qpl.START_DATE_ACTIVE sda,
87: qpl.END_DATE_ACTIVE eda, qph.active_flag
88: FROM qp_list_lines qpl, qp_list_headers_all_b qph
89: WHERE pricing_phase_id = 1
84: -- Price List Lines
85: SELECT /*+ ordered USE_NL(qpl qph) index(qpl QP_LIST_LINES_N1) */
86: qpl.pricing_phase_id, qpl.pattern_id, qpl.START_DATE_ACTIVE sda,
87: qpl.END_DATE_ACTIVE eda, qph.active_flag
88: FROM qp_list_lines qpl, qp_list_headers_all_b qph
89: WHERE pricing_phase_id = 1
90: AND qpl.list_header_id = p_list_header_id --NVL(p_list_header_id, qpl.list_header_id)
91: AND qph.list_header_id = qpl.list_header_id
92: UNION ALL
133: -- Price List Lines
134: SELECT /*+ ordered */
135: qpl.pricing_phase_id, qpl.pattern_id, qpl.START_DATE_ACTIVE sda,
136: qpl.END_DATE_ACTIVE eda, qph.active_flag
137: FROM qp_list_lines qpl, qp_list_headers_all_b qph
138: WHERE pricing_phase_id = 1
139: AND qph.list_header_id = qpl.list_header_id
140: UNION ALL
141: -- Price List headers
195: l_start_date_tbl,
196: l_end_date_tbl,
197: l_active_flag_tbl
198: --bug 9594320 end
199: FROM qp_list_lines qpl, qp_list_headers_all_b qph
200: WHERE qpl.list_line_id = p_min_list_line_id
201: AND qph.list_header_id = qpl.list_header_id;
202:
203: ELSIF g_qp_pattern_search IN ('M','B') AND g_list_type NOT IN ('PRL','AGR','PML') THEN -- IF 1.1
410: p_list_header_id IN number default null,
411: p_no_of_threads IN NUMBER default 1)
412: is
413: cursor list_line is
414: select list_line_id from qp_list_lines where qualification_ind in (8,10,12,14,28,30, 4,6,20,22,2,0)
415: and list_header_id = nvl(p_list_header_id, list_header_id)
416: order by list_line_id;
417:
418: l_gap number := 0;
489: p_low_list_line_id IN NUMBER default null,
490: p_high_list_line_id IN NUMBER default null)
491: is
492: cursor list_line is
493: select list_line_id from qp_list_lines where qualification_ind in (8,10,12,14,28,30, 4,6,20,22,2,0)
494: and list_line_id BETWEEN p_low_list_line_id AND p_high_list_line_id
495: order by list_line_id;
496:
497: l_gap number := 5000;
591: update qp_attribute_groups
592: set pattern_id = l_min_pattern_id
593: where pattern_id = g_pattern_pattern_id_final_tbl(i);
594: ELSIF g_pattern_pat_type_final_tbl(i) = 'PP' then
595: update /*+ index(lines QP_LIST_LINES_N9) */ qp_list_lines lines
596: set pattern_id = l_min_pattern_id
597: where pattern_id = g_pattern_pattern_id_final_tbl(i);
598: else
599: update qp_attribute_groups
645: union all
646: -- Price List Lines
647: select /*+ ordered */ qpl.pricing_phase_id, qpl.pattern_id, qpl.START_DATE_ACTIVE sda,
648: qpl.END_DATE_ACTIVE eda, qph.active_flag
649: from qp_list_lines qpl, qp_list_headers_all_b qph
650: where pricing_phase_id = 1
651: and qph.list_header_id = qpl.list_header_id
652: union ALL
653: -- Price List headers
774: from qp_pricing_attributes
775: where /*list_line_id in (
776: select list_line_id from qp_attribute_groups where eq_flag = 'N'
777: UNION
778: select list_line_id from qp_list_lines where eq_flag = 'N')
779: and */
780: --list_header_id = nvl(p_list_header_id, list_header_id)
781: comparison_operator_code <> '='
782: and pricing_segment_id is not NULL
785: from qp_qualifiers
786: where /*list_line_id in (
787: select list_line_id from qp_attribute_groups where eq_flag = 'N'
788: UNION
789: select list_line_id from qp_list_lines where eq_flag = 'N')
790: and */
791: --list_header_id = nvl(p_list_header_id, list_header_id)
792: comparison_operator_code <> '='
793: and segment_id is not null);
800: from qp_pricing_attributes
801: where /*list_line_id in (
802: select list_line_id from qp_attribute_groups where eq_flag = 'N'
803: UNION
804: select list_line_id from qp_list_lines where eq_flag = 'N')
805: and */
806: list_header_id = p_list_header_id
807: --AND comparison_operator_code <> '='
808: and pricing_segment_id is not NULL
811: from qp_qualifiers
812: where /*list_line_id in (
813: select list_line_id from qp_attribute_groups where eq_flag = 'N'
814: UNION
815: select list_line_id from qp_list_lines where eq_flag = 'N')
816: and */
817: list_header_id = p_list_header_id
818: --AND comparison_operator_code <> '='
819: and segment_id is not null);
824: present for lines or headers for that phase and will poulate 'Y' for PRIC_PROD_ATTR_ONLY_FLAG*/
825: IF p_list_header_id IS NULL THEN
826: insert into qp_list_header_phases (list_header_id,pricing_phase_id,PRIC_PROD_ATTR_ONLY_FLAG)
827: (select distinct list_header_id, pricing_phase_id,'Y'
828: from qp_list_lines
829: where pricing_phase_id > 1
830: and qualification_ind in (0,4,20)
831: MINUS
832: SELECT distinct list_header_id, pricing_phase_id,'Y'
833: FROM qp_list_header_phases
834: );
835:
836: -- bug 12731268
837: UPDATE qp_list_lines
838: SET pattern_id=NULL
839: WHERE pattern_id IN (-2,-3)
840: AND list_line_type_code IN ('PLL','PBH');
841:
841:
842: ELSE
843: insert into qp_list_header_phases (list_header_id,pricing_phase_id,PRIC_PROD_ATTR_ONLY_FLAG)
844: (select distinct list_header_id, pricing_phase_id,'Y'
845: from qp_list_lines
846: where pricing_phase_id > 1
847: and qualification_ind in (0,4,20)
848: and list_header_id = nvl(p_list_header_id, list_header_id)
849: MINUS
852: where list_header_id = nvl(p_list_header_id, list_header_id)
853: );
854:
855: -- bug 12731268
856: UPDATE qp_list_lines
857: SET pattern_id=NULL
858: WHERE pattern_id IN (-2,-3)
859: AND list_line_type_code IN ('PLL','PBH')
860: AND list_header_id = p_list_header_id;
905: g_pattern_upg_slab_table.delete;
906: IF p_list_header_id IS NULL THEN
907: select count(*)
908: into l_total_lines
909: from qp_list_lines
910: where qualification_ind in (8,10,12,14,28,30, 4,6,20,22,2,0);
911: ELSE
912: select count(*)
913: into l_total_lines
910: where qualification_ind in (8,10,12,14,28,30, 4,6,20,22,2,0);
911: ELSE
912: select count(*)
913: into l_total_lines
914: from qp_list_lines
915: where qualification_ind in (8,10,12,14,28,30, 4,6,20,22,2,0)
916: and list_header_id = p_list_header_id;
917: END IF;
918: write_log( 'l_total_lines ' || l_total_lines);
957:
958: else -- if l_total_lines > 0
959: if p_list_header_id IS not null then
960: -- like upgrade, refresh everything related to patterns
961: /* update qp_list_lines
962: set pattern_id = null,
963: pricing_attribute_count = null,
964: product_uom_code = null,
965: hash_key = null,
972: delete from qp_attribute_groups
973: where list_header_id = p_list_header_id
974: and list_line_id <> -1;
975:
976: /* update qp_list_lines
977: set pattern_id = null,
978: pricing_attribute_count = null,
979: product_uom_code = null,
980: hash_key = null,
991: write_log( 'Start time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
992: if p_list_header_id is NOT null then
993: -- like upgrade, refresh everything related to patterns
994: -- IF g_qp_pattern_search <> 'M' THEN
995: /* update /*+ index_asc(lines QP_LIST_LINES_PK) */ /*qp_list_lines lines
996: set pattern_id = null,
997: pricing_attribute_count = null,
998: product_uom_code = null,
999: hash_key = null,
1007: delete from qp_attribute_groups
1008: where list_header_id = p_list_header_id
1009: and list_line_id between p_low_list_line_id and p_high_list_line_id;
1010: --IF g_qp_pattern_search <> 'M' THEN
1011: /*update /*+ index_asc(lines QP_LIST_LINES_N18) *//* qp_list_lines lines
1012: set pattern_id = null,
1013: pricing_attribute_count = null,
1014: product_uom_code = null,
1015: hash_key = null,
1276: ,p_high_list_line_id IN NUMBER)
1277: is
1278: cursor c_attr_grp_lq_csr is
1279: select * from
1280: (select /*+ ordered use_nl(qpq, qph) index(qpl QP_LIST_LINES_N18) c_attr_grp_lq_csr*/ qpq.list_header_id,
1281: qpq.list_line_id,
1282: qpq.segment_id,
1283: qph.active_flag, --bug#11927380
1284: qpq.list_type_code,
1318: qpl.modifier_level_code modifier_level_code,
1319: qpq.qualifier_datatype attribute_datatype,
1320: qpq.qualifier_attr_value attribute_value,
1321: 'QUAL' attribute_type
1322: from qp_list_headers_all_b qph, qp_list_lines qpl, qp_qualifiers qpq
1323: where qph.list_header_id = p_list_header_id
1324: AND qpl.list_header_id = qph.list_header_id
1325: and qph.list_header_id = qpq.list_header_id
1326: and qpl.list_line_id = qpq.list_line_id
1335: AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
1336: OR (g_qp_pattern_search = 'M' AND qph.list_type_code NOT IN ('PRL','AGR','PML'))
1337: OR g_qp_pattern_search = 'B' AND qph.list_type_code NOT IN ('PML'))
1338: union
1339: select /*+ ordered use_nl(qpa, qph) index(qpl QP_LIST_LINES_N18) c_attr_grp_lq_csr*/ distinct qpl.list_header_id,
1340: qpl.list_line_id,
1341: qpa.product_segment_id segment_id,
1342: qph.active_flag,
1343: qph.list_type_code,
1378: qpl.modifier_level_code modifier_level_code,
1379: qpa.product_attribute_datatype attribute_datatype,
1380: qpa.product_attr_value attribute_value,
1381: 'PROD' attribute_type
1382: from qp_list_headers_all_b qph, qp_list_lines qpl, qp_pricing_attributes qpa
1383: where qph.list_header_id = p_list_header_id
1384: AND qpl.list_header_id = qph.list_header_id
1385: and qpl.list_line_id = qpa.list_line_id
1386: and qpa.excluder_flag = 'N'
1394: AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
1395: OR (g_qp_pattern_search = 'M' AND qph.list_type_code NOT IN ('PRL','AGR','PML'))
1396: OR g_qp_pattern_search = 'B' AND qph.list_type_code NOT IN ('PML'))
1397: union
1398: select /*+ ordered use_nl(qpa, qph) index(qpl QP_LIST_LINES_N18) c_attr_grp_lq_csr*/ qpl.list_header_id,
1399: qpl.list_line_id,
1400: qpa.pricing_segment_id segment_id,
1401: qph.active_flag,
1402: qph.list_type_code,
1437: qpl.modifier_level_code modifier_level_code,
1438: qpa.pricing_attribute_datatype attribute_datatype,
1439: qpa.pricing_attr_value_from attribute_value,
1440: 'PRIC' attribute_type
1441: from qp_list_headers_all_b qph, qp_list_lines qpl, qp_pricing_attributes qpa
1442: where qph.list_header_id = p_list_header_id
1443: AND qph.list_header_id = qpl.list_header_id
1444: and qpl.list_line_id = qpa.list_line_id
1445: and qpl.pricing_phase_id > 1
1452: ) attr_view
1453: order by attr_view.list_header_id, attr_view.list_line_id, attr_view.segment_id;
1454: cursor c_attr_grp_lq_csr_hdr_null is
1455: select * from
1456: (select /*+ ordered use_nl(qpq, qph) index(qpl QP_LIST_LINES_N18) c_attr_grp_lq_csr_hdr_null*/ qpq.list_header_id,
1457: qpq.list_line_id,
1458: qpq.segment_id,
1459: qph.active_flag,
1460: qpq.list_type_code,
1494: qpl.modifier_level_code modifier_level_code,
1495: qpq.qualifier_datatype attribute_datatype,
1496: qpq.qualifier_attr_value attribute_value,
1497: 'QUAL' attribute_type
1498: from qp_list_headers_all_b qph, qp_list_lines qpl, qp_qualifiers qpq
1499: where qpl.list_header_id = qph.list_header_id
1500: and qph.list_header_id = qpq.list_header_id
1501: and qpl.list_line_id = qpq.list_line_id
1502: AND qpq.list_line_id <> -1
1510: AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
1511: OR (g_qp_pattern_search = 'M' AND qph.list_type_code NOT IN ('PRL','AGR','PML'))
1512: OR g_qp_pattern_search = 'B' AND qph.list_type_code NOT IN ('PML'))
1513: union
1514: select /*+ ordered use_nl(qpa, qph) index(qpl QP_LIST_LINES_N18) c_attr_grp_lq_csr_hdr_null*/ distinct qpl.list_header_id,
1515: qpl.list_line_id,
1516: qpa.product_segment_id segment_id,
1517: qph.active_flag,
1518: qph.list_type_code,
1553: qpl.modifier_level_code modifier_level_code,
1554: qpa.product_attribute_datatype attribute_datatype,
1555: qpa.product_attr_value attribute_value,
1556: 'PROD' attribute_type
1557: from qp_list_headers_all_b qph, qp_list_lines qpl, qp_pricing_attributes qpa
1558: where qpl.list_header_id = qph.list_header_id
1559: and qpl.list_line_id = qpa.list_line_id
1560: and qpa.excluder_flag = 'N'
1561: and qpl.pricing_phase_id > 1
1568: AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
1569: OR (g_qp_pattern_search = 'M' AND qph.list_type_code NOT IN ('PRL','AGR','PML'))
1570: OR g_qp_pattern_search = 'B' AND qph.list_type_code NOT IN ('PML'))
1571: union
1572: select /*+ ordered use_nl(qpa, qph) index(qpl QP_LIST_LINES_N18) c_attr_grp_lq_csr_hdr_null*/ qpl.list_header_id,
1573: qpl.list_line_id,
1574: qpa.pricing_segment_id segment_id,
1575: qph.active_flag,
1576: qph.list_type_code,
1611: qpl.modifier_level_code modifier_level_code,
1612: qpa.pricing_attribute_datatype attribute_datatype,
1613: qpa.pricing_attr_value_from attribute_value,
1614: 'PRIC' attribute_type
1615: from qp_list_headers_all_b qph, qp_list_lines qpl, qp_pricing_attributes qpa
1616: where qph.list_header_id = qpl.list_header_id
1617: and qpl.list_line_id = qpa.list_line_id
1618: and qpl.pricing_phase_id > 1
1619: and qpl.qualification_ind in (8,10,12,14,28,30)
1748: IS
1749:
1750: cursor c_lines_pp_csr is
1751: select * from
1752: (select /*+ ordered use_nl(qpa) index(qpl QP_LIST_LINES_N18) c_lines_pp_csr*/ distinct qpa.list_header_id,
1753: qpa.list_line_id,
1754: qpa.product_segment_id segment_id,
1755: '=' comparison_operator_code,
1756: qpa.pricing_phase_id,
1795: ---- Added for PL/SQL Pattern Search
1796:
1797: 'PROD' attribute_type
1798: from qp_list_headers_all_b qph,
1799: qp_list_lines qpl,
1800: qp_pricing_attributes qpa
1801: where qph.list_header_id = p_list_header_id
1802: AND qph.list_header_id = qpl.list_header_id
1803: AND qpl.list_line_id = qpa.list_line_id
1811: AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
1812: OR (g_qp_pattern_search = 'M' AND qph.list_type_code NOT IN ('PRL','AGR','PML'))
1813: OR g_qp_pattern_search = 'B' AND qph.list_type_code NOT IN ('PML'))
1814: union
1815: select /*+ ordered use_nl(qpa) index(qpl QP_LIST_LINES_N18) c_lines_pp_csr*/ qpa.list_header_id,
1816: qpa.list_line_id,
1817: qpa.pricing_segment_id segment_id,
1818: qpa.comparison_operator_code,
1819: qpa.pricing_phase_id,
1858: ---- Added for PL/SQL Pattern Search
1859:
1860: 'PRIC' attribute_type
1861: from qp_list_headers_all_b qph,
1862: qp_list_lines qpl,
1863: qp_pricing_attributes qpa
1864: where qph.list_header_id = p_list_header_id
1865: AND qpl.list_header_id = qph.list_header_id
1866: AND qpl.list_line_id = qpa.list_line_id
1870: AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
1871: OR (g_qp_pattern_search = 'M' AND qph.list_type_code NOT IN ('PRL','AGR','PML'))
1872: OR g_qp_pattern_search = 'B' AND qph.list_type_code NOT IN ('PML'))
1873: union
1874: select /*+ ordered use_nl(qpa) index(qpl QP_LIST_LINES_N18) c_lines_pp_csr*/ distinct qph.list_header_id,
1875: qpl.list_line_id,
1876: NULL segment_id,
1877: 'BLIND' comparison_operator_code,
1878: qpl.pricing_phase_id,
1918: ---- Added for PL/SQL Pattern Search
1919:
1920: 'BLIN' attribute_type
1921: from qp_list_headers_all_b qph,
1922: qp_list_lines qpl
1923: where qph.list_header_id = p_list_header_id
1924: AND qpl.list_header_id = qph.list_header_id
1925: and qpl.qualification_ind in (0,2)
1926: and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
1930: ) attr_view
1931: order by attr_view.list_header_id, attr_view.list_line_id, attr_view.segment_id;
1932: cursor c_lines_pp_csr_hdr_null is
1933: select * from
1934: (select /*+ ordered use_nl(qpa) index(qpl QP_LIST_LINES_N18) c_lines_pp_csr_hdr_null*/ distinct qpa.list_header_id,
1935: qpa.list_line_id,
1936: qpa.product_segment_id segment_id,
1937: '=' comparison_operator_code,
1938: qpa.pricing_phase_id,
1976:
1977: ---- Added for PL/SQL Pattern Search
1978: 'PROD' attribute_type
1979: from qp_list_headers_all_b qph,
1980: qp_list_lines qpl,
1981: qp_pricing_attributes qpa
1982: where qph.list_header_id = qpl.list_header_id
1983: AND qpl.list_line_id = qpa.list_line_id
1984: and qpa.excluder_flag = 'N'
1991: AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
1992: OR (g_qp_pattern_search = 'M' AND qph.list_type_code NOT IN ('PRL','AGR','PML'))
1993: OR g_qp_pattern_search = 'B' AND qph.list_type_code NOT IN ('PML'))
1994: union
1995: select /*+ ordered use_nl(qpa) index(qpl QP_LIST_LINES_N18) c_lines_pp_csr_hdr_null*/ qpa.list_header_id,
1996: qpa.list_line_id,
1997: qpa.pricing_segment_id segment_id,
1998: qpa.comparison_operator_code,
1999: qpa.pricing_phase_id,
2036: qpl.automatic_flag,
2037: ---- Added for PL/SQL Pattern Search
2038: 'PRIC' attribute_type
2039: from qp_list_headers_all_b qph,
2040: qp_list_lines qpl,
2041: qp_pricing_attributes qpa
2042: where qpl.list_header_id = qph.list_header_id
2043: AND qpl.list_line_id = qpa.list_line_id
2044: and qpl.qualification_ind in (20,22)
2047: AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
2048: OR (g_qp_pattern_search = 'M' AND qph.list_type_code NOT IN ('PRL','AGR','PML'))
2049: OR g_qp_pattern_search = 'B' AND qph.list_type_code NOT IN ('PML'))
2050: union
2051: select /*+ ordered use_nl(qpa) index(qpl QP_LIST_LINES_N18) c_lines_pp_csr_hdr_null*/ distinct qph.list_header_id,
2052: qpl.list_line_id,
2053: NULL segment_id,
2054: 'BLIND' comparison_operator_code,
2055: qpl.pricing_phase_id,
2092: qpl.automatic_flag,
2093: ---- Added for PL/SQL Pattern Search
2094: 'BLIN' attribute_type
2095: from qp_list_headers_all_b qph,
2096: qp_list_lines qpl
2097: where qpl.list_header_id = qph.list_header_id
2098: and qpl.qualification_ind in (0,2)
2099: and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
2100: AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
2847:
2848: -- insert into qp_attribute_groups from final tables
2849: populate_atgrps;
2850:
2851: -- for line pattern, qp_list_lines.cache_key need to be populated as well
2852: IF g_qp_pattern_search <> 'M' THEN ----
2853: if p_pattern_type = 'LP' then
2854: update_list_lines_cache_key;
2855: end if;
3114: Move_data_from_tmp_to_final(p_pattern_type);
3115:
3116: oe_debug_pub.add('Count final table after move data - '||g_list_header_id_final_tbl.count);
3117:
3118: -- update qp_list_lines from final tables
3119: IF g_qp_pattern_search <> 'M' THEN
3120: update_list_lines;
3121: END IF;
3122:
3287: if g_cache_key_tmp_tbl(grp_no_index) is null and p_pattern_type in ('LP', 'PP') then
3288: g_cache_key_final_tbl(l_atgrp_final_index) := g_list_header_id_tmp_tbl(grp_no_index);
3289:
3290: select product_precedence into l_product_precedence
3291: from qp_list_lines where list_line_id = g_list_line_id_final_tbl(l_atgrp_final_index);
3292:
3293: if (l_product_precedence is not null) and (g_effec_precedence_final_tbl(l_atgrp_final_index) is not null)
3294: and (l_product_precedence < g_effec_precedence_final_tbl(l_atgrp_final_index)) then
3295: g_effec_precedence_final_tbl(l_atgrp_final_index) := l_product_precedence;
3460: end if;
3461: raise;
3462: END Populate_Patterns;
3463:
3464: -- bulk update qp_list_lines table
3465: PROCEDURE update_list_lines
3466: is
3467: BEGIN
3468:
3466: is
3467: BEGIN
3468:
3469: FORALL i in 1 .. g_list_line_id_final_tbl.count
3470: UPDATE /*+ index(lines QP_LIST_LINES_PK) */ qp_list_lines lines
3471: set pattern_id = g_pattern_id_final_tbl(i),
3472: product_uom_code = g_product_uom_code_final_tbl(i),
3473: pricing_attribute_count = g_pricing_attr_count_final_tbl(i),
3474: hash_key = g_hash_key_final_tbl(i),
3490: present for lines or headers for that phase and will poulate 'Y' for PRIC_PROD_ATTR_ONLY_FLAG*/
3491:
3492: FORALL i in 1 .. g_list_line_id_final_tbl.COUNT
3493: insert into qp_list_header_phases (list_header_id,pricing_phase_id,PRIC_PROD_ATTR_ONLY_FLAG)
3494: (select /*+ index(QP_LIST_LINES QP_LIST_LINES_U1) */ distinct list_header_id, pricing_phase_id,'Y'
3495: from qp_list_lines
3496: where pricing_phase_id > 1
3497: and qualification_ind in (0,4,20)
3498: and list_header_id = g_list_header_id_final_tbl(i)
3491:
3492: FORALL i in 1 .. g_list_line_id_final_tbl.COUNT
3493: insert into qp_list_header_phases (list_header_id,pricing_phase_id,PRIC_PROD_ATTR_ONLY_FLAG)
3494: (select /*+ index(QP_LIST_LINES QP_LIST_LINES_U1) */ distinct list_header_id, pricing_phase_id,'Y'
3495: from qp_list_lines
3496: where pricing_phase_id > 1
3497: and qualification_ind in (0,4,20)
3498: and list_header_id = g_list_header_id_final_tbl(i)
3499: MINUS
3628:
3629: end populate_pp_atgrps;
3630:
3631: --------Added for PL/SQL Pattern Search
3632: -- bulk update qp_list_lines.cache_key for line patterns
3633: PROCEDURE update_list_lines_cache_key
3634: is
3635: BEGIN
3636:
3634: is
3635: BEGIN
3636:
3637: FORALL i in 1 .. g_list_line_id_final_tbl.count
3638: UPDATE qp_list_lines
3639: set cache_key = g_cache_key_final_tbl(i),
3640: last_update_date = g_last_update_date_final_tbl(i),
3641: last_updated_by = g_last_updated_by_final_tbl(i),
3642: last_update_login = g_last_update_login_final_tbl(i)
3771: present for lines or headers for that phase and will poulate 'Y' for PRIC_PROD_ATTR_ONLY_FLAG*/
3772:
3773: FORALL i in 1 .. g_list_line_id_final_tbl.COUNT
3774: insert into qp_list_header_phases (list_header_id,pricing_phase_id,PRIC_PROD_ATTR_ONLY_FLAG)
3775: (select /*+ index(QP_LIST_LINES QP_LIST_LINES_U1) */ distinct list_header_id, pricing_phase_id,'Y'
3776: from qp_list_lines
3777: where pricing_phase_id > 1
3778: and qualification_ind in (0,4,20)
3779: and list_header_id = g_list_header_id_final_tbl(i)
3772:
3773: FORALL i in 1 .. g_list_line_id_final_tbl.COUNT
3774: insert into qp_list_header_phases (list_header_id,pricing_phase_id,PRIC_PROD_ATTR_ONLY_FLAG)
3775: (select /*+ index(QP_LIST_LINES QP_LIST_LINES_U1) */ distinct list_header_id, pricing_phase_id,'Y'
3776: from qp_list_lines
3777: where pricing_phase_id > 1
3778: and qualification_ind in (0,4,20)
3779: and list_header_id = g_list_header_id_final_tbl(i)
3780: MINUS
4546: from qp_pricing_attributes
4547: where /*list_line_id in (
4548: select list_line_id from qp_attribute_groups where eq_flag = 'N'
4549: UNION
4550: select list_line_id from qp_list_lines where eq_flag = 'N')
4551: and */list_header_id = nvl(p_list_header_id, list_header_id)
4552: --AND comparison_operator_code <> '='
4553: and pricing_segment_id is not null
4554: UNION all
4556: from qp_qualifiers
4557: where /*list_line_id in (
4558: select list_line_id from qp_attribute_groups where eq_flag = 'N'
4559: UNION
4560: select list_line_id from qp_list_lines where eq_flag = 'N')
4561: and */list_header_id = nvl(p_list_header_id, list_header_id)
4562: --AND comparison_operator_code <> '='
4563: and segment_id is not null);
4564:
4587: l_status_code VARCHAR2(30) := NULL;
4588: l_status_text VARCHAR2(2000) := NULL;
4589: l_pid NUMBER := NULL;
4590: l_qual_exists varchar2(1) := 'N';
4591: l_line_LIMIT_EXISTS qp_list_lines.LIMIT_EXISTS_FLAG%type;
4592:
4593: BEGIN
4594: g_qp_pattern_search := FND_PROFILE.VALUE('QP_PATTERN_SEARCH');
4595: g_call_from_setup := 'Y';
4644: p_list_line_id, p_list_line_id);
4645: elsif p_setup_action = 'U' or p_setup_action = 'D' then
4646: -- update or delete case
4647: if p_qualifier_group is null then
4648: update qp_list_lines
4649: set pattern_id = DECODE(qualification_ind,0,-2,-3),
4650: hash_key = null,
4651: cache_key = null
4652: where list_line_id = p_list_line_id
4668: end;
4669:
4670: if l_qual_exists = 'Y' then
4671: -- means some qualifiers still exist for p_qualifier_group
4672: update qp_list_lines
4673: set pattern_id = DECODE(qualification_ind,0,-2,-3),
4674: hash_key = null,
4675: cache_key = null
4676: where list_line_id = p_list_line_id
4694: if l_qual_exists = 'N' then
4695: -- no qualifiers exist other than -1 qualifier_grouping_no
4696: -- this may insert in qp_attribute_groups with LINE_QUAL_GROUPING_NO = -1, if any
4697: -- qualifiers exist with qualifier_grouping_no = -1
4698: update qp_list_lines
4699: set pattern_id = DECODE(qualification_ind,0,-2,-3),
4700: hash_key = null,
4701: cache_key = null
4702: where list_line_id = p_list_line_id
4709: elsif p_setup_action = 'UD' then
4710: begin
4711: select LIMIT_EXISTS_FLAG
4712: into l_line_LIMIT_EXISTS
4713: from qp_list_lines
4714: where list_line_id = p_list_line_id;
4715:
4716: -- update line pattern records
4717: update qp_attribute_groups
4753: l_qual_exists := 'Y';
4754: end if;
4755:
4756: /* if p_setup_action = 'I' then
4757: -- assume LP exists, and so nullify the PP values in qp_list_lines table
4758: --except cache_key
4759: update /*+ index(lines QP_LIST_LINES_PK) */ /*qp_list_lines
4760: set pattern_id = null,
4761: pricing_attribute_count = null,
4755:
4756: /* if p_setup_action = 'I' then
4757: -- assume LP exists, and so nullify the PP values in qp_list_lines table
4758: --except cache_key
4759: update /*+ index(lines QP_LIST_LINES_PK) */ /*qp_list_lines
4760: set pattern_id = null,
4761: pricing_attribute_count = null,
4762: product_uom_code = null,
4763: hash_key = null
4796: from qp_pricing_attributes
4797: where /*list_line_id in (
4798: select list_line_id from qp_attribute_groups where eq_flag = 'N'
4799: UNION
4800: select list_line_id from qp_list_lines where eq_flag = 'N')
4801: and */list_header_id = nvl(p_list_header_id, list_header_id)
4802: --AND comparison_operator_code <> '='
4803: and pricing_segment_id is not null
4804: UNION all
4806: from qp_qualifiers
4807: where /*list_line_id in (
4808: select list_line_id from qp_attribute_groups where eq_flag = 'N'
4809: UNION
4810: select list_line_id from qp_list_lines where eq_flag = 'N')
4811: and */list_header_id = nvl(p_list_header_id, list_header_id)
4812: --AND comparison_operator_code <> '='
4813: and segment_id is not null);
4814:
4835:
4836: l_status_code VARCHAR2(30) := NULL;
4837: l_status_text VARCHAR2(2000) := NULL;
4838: l_qual_exists varchar2(1) := 'N';
4839: l_product_uom_code qp_list_lines.product_uom_code%type;
4840: l_qual_ind number;
4841:
4842: BEGIN
4843: g_qp_pattern_search := FND_PROFILE.VALUE('QP_PATTERN_SEARCH');
4854: end if;
4855:
4856: select qualification_ind
4857: into l_qual_ind
4858: from qp_list_lines
4859: where list_line_id = p_list_line_id;
4860: IF p_list_header_id IS NOT NULL THEN
4861: SELECT list_type_code INTO g_list_type FROM qp_list_headers WHERE list_header_id = p_list_header_id;
4862: ELSIF g_qp_pattern_search = 'P' THEN
4874: return;
4875: end if;
4876:
4877: if p_setup_action = 'UD' then
4878: -- update qp_list_lines.product_uom_code
4879: begin
4880: select product_uom_code
4881: into l_product_uom_code
4882: from qp_pricing_attributes
4888: when no_data_found then
4889: l_product_uom_code := null;
4890: end;
4891:
4892: update /*+ index(lines QP_LIST_LINES_PK) */ qp_list_lines
4893: set product_uom_code = l_product_uom_code
4894: where list_line_id = p_list_line_id;
4895: else
4896: -- when called while set up of price list/modifier
4963: from qp_pricing_attributes
4964: where /*list_line_id in (
4965: select list_line_id from qp_attribute_groups where eq_flag = 'N'
4966: UNION
4967: select list_line_id from qp_list_lines where eq_flag = 'N')
4968: and */list_header_id = nvl(p_list_header_id, list_header_id)
4969: --AND comparison_operator_code <> '='
4970: and pricing_segment_id is not null
4971: UNION all
4973: from qp_qualifiers
4974: where /*list_line_id in (
4975: select list_line_id from qp_attribute_groups where eq_flag = 'N'
4976: UNION
4977: select list_line_id from qp_list_lines where eq_flag = 'N')
4978: and */list_header_id = nvl(p_list_header_id, list_header_id)
4979: --AND comparison_operator_code <> '='
4980: and segment_id is not null);
4981:
4997:
4998: procedure Remove_Prod_Pattern_for_Line(p_list_line_id IN NUMBER)
4999: is
5000: begin
5001: /* update /*+ index(lines QP_LIST_LINES_PK) */ /*qp_list_lines
5002: set pattern_id = null,
5003: pricing_attribute_count = null,
5004: product_uom_code = null,
5005: hash_key = null,