[Home] [Help]
753: -- Changed the cursor l_outer_qual_cur for bug#3002632
754: --Added index hints for bug#7480862
755: CURSOR l_MINI_OUTER_QUAL_CUR IS
756: -- Qualifier Only
757: select /*+ ORDERED NO_EXPAND USE_NL(qpq qplatq qph) index (qplatq,QP_PREQ_LINE_ATTRS_TMP_N6)
758: index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_outer_qual_cur_mod */
759: qpq.list_header_id,
760: qpq.list_line_id list_line_id,
761: qpq.qualifier_group_cnt,
854: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
855: --and qptq.pricing_status_code = G_STATUS_UNCHANGED
856: --and qptq.pricing_phase_id = p_pricing_phase_id
857: union all
858: select /*+ ORDERED USE_NL(qpq qplatq qph) index (qplatq,QP_PREQ_LINE_ATTRS_TMP_N6) index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_o
859: uter_qual_cur_mod */
860: qpq.list_header_id,
861: qpq.list_line_id list_line_id,
862: qpq.qualifier_group_cnt,
954: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
955: --and qptq.pricing_status_code = G_STATUS_UNCHANGED
956: --and qptq.pricing_phase_id = p_pricing_phase_id
957: union all
958: select /*+ ORDERED USE_NL(qpq qplatq qph) index (qplatq,QP_PREQ_LINE_ATTRS_TMP_N6) index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_outer_qual_cur_mod */
959: qpq.list_header_id,
960: qpq.list_line_id list_line_id,
961: qpq.qualifier_group_cnt,
962: qpq.others_group_cnt,
1056: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
1057: --and qptq.pricing_status_code = G_STATUS_UNCHANGED
1058: --and qptq.pricing_phase_id = p_pricing_phase_id
1059: union all
1060: select /*+ ORDERED USE_NL(qpq qplatq qph) index (qplatq,QP_PREQ_LINE_ATTRS_TMP_N6) index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_ou
1061: ter_qual_cur_mod */
1062: qpq.list_header_id,
1063: qpq.list_line_id list_line_id,
1064: qpq.qualifier_group_cnt,
1157: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
1158: --and qptq.pricing_status_code = G_STATUS_UNCHANGED
1159: --and qptq.pricing_phase_id = p_pricing_phase_id
1160: union all
1161: select /*+ ORDERED USE_NL(qpq qplatq qph) index (qplatq,QP_PREQ_LINE_ATTRS_TMP_N6) index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_outer_qual_cur_mod */
1162: qpq.list_header_id,
1163: qpq.list_line_id list_line_id,
1164: qpq.qualifier_group_cnt,
1165: qpq.others_group_cnt,
1260: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
1261: --and qptq.pricing_status_code = G_STATUS_UNCHANGED
1262: --and qptq.pricing_phase_id = p_pricing_phase_id
1263: union all
1264: select /*+ ORDERED USE_NL(qpq qplh qph) index (qplatq,QP_PREQ_LINE_ATTRS_TMP_N6) index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_out
1265: er_qual_cur_mod */
1266: qpq.list_header_id,
1267: qpq.list_line_id list_line_id,
1268: qpq.qualifier_group_cnt,
1362: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
1363: --and qptq.pricing_status_code = G_STATUS_UNCHANGED
1364: --and qptq.pricing_phase_id = p_pricing_phase_id
1365: union all
1366: select /*+ ORDERED USE_NL(qpq qplh qph) index (qplatq,QP_PREQ_LINE_ATTRS_TMP_N7)
1367: index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_outer_qual_cur_mod */
1368: qpq.list_header_id,
1369: qpq.list_line_id list_line_id,
1370: qpq.qualifier_group_cnt,
1463: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
1464: --and qptq.pricing_status_code = G_STATUS_UNCHANGED
1465: --and qptq.pricing_phase_id = p_pricing_phase_id
1466: union all
1467: select /*+ ORDERED USE_NL(qpq qplh qph) index (qplatq,QP_PREQ_LINE_ATTRS_TMP_N7)
1468: index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_outer_qual_cur_mod */
1469: qpq.list_header_id,
1470: qpq.list_line_id list_line_id,
1471: qpq.qualifier_group_cnt,
1763: -- [julin/4396043/4471795] perform header filter before prod check, go to qp_pricing_attributes
1764: -- before qp_npreq_line_attrs_tmp during prod check.
1765: CURSOR l_OUTER_QUAL_CUR IS
1766: -- Qualifier Only
1767: select /*+ ORDERED USE_NL(qpq qplh qph) index(qpq qp_qualifiers_n4) index(qph qp_list_headers_b_n7) index(qplatq qp_preq_line_attrs_tmp_n2) l_outer_qual_cur_mod */
1768: qpq.list_header_id,
1769: qpq.list_line_id list_line_id,
1770: qpq.qualifier_group_cnt,
1771: qpq.others_group_cnt,
1885: and qph.source_system_code = qprs.source_system_code
1886: and ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') = 'N')
1887: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
1888: union all
1889: select /*+ ORDERED USE_NL(qpq qplh qph) index(qpq qp_qualifiers_n6) index(qph qp_list_headers_b_n7) index(qplatq qp_preq_line_attrs_tmp_n2) l_outer_qual_cur_mod */
1890: qpq.list_header_id,
1891: qpq.list_line_id list_line_id,
1892: qpq.qualifier_group_cnt,
1893: qpq.others_group_cnt,
2009: and qph.source_system_code = qprs.source_system_code
2010: and ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') = 'N')
2011: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
2012: union all
2013: select /*+ ORDERED USE_NL(qpq qplh qph) index(qpq qp_qualifiers_n5) index(qph qp_list_headers_b_n7) index(qplatq qp_preq_line_attrs_tmp_n2) l_outer_qual_cur_mod */
2014: qpq.list_header_id,
2015: qpq.list_line_id list_line_id,
2016: qpq.qualifier_group_cnt,
2017: qpq.others_group_cnt,
2134: and qph.source_system_code = qprs.source_system_code
2135: and ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') = 'N')
2136: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
2137: union all
2138: select /*+ ORDERED USE_NL(qpq qplh qph) index(qpq qp_qualifiers_n5) index(qph qp_list_headers_b_n7) index(qplatq qp_preq_line_attrs_tmp_n2) l_outer_qual_cur_mod */
2139: qpq.list_header_id,
2140: qpq.list_line_id list_line_id,
2141: qpq.qualifier_group_cnt,
2142: qpq.others_group_cnt,
2483: */
2484: -- Changed the cursor l_outer_qual_cur for bug#3002632
2485: CURSOR l_OUTER_QUAL_CUR IS
2486: -- Qualifier Only
2487: select /*+ ORDERED USE_NL(qpq qplh qph) index(qpq qp_qualifiers_n4) index(qph qp_list_headers_b_n7) index(qplatq qp_preq_line_attrs_tmp_n2) dynamic_sampling(1) l_header_outer_qual_cur_mod */
2488: qpq.list_header_id,
2489: qpq.list_line_id list_line_id,
2490: qpq.qualifier_group_cnt,
2491: qpq.others_group_cnt,
2586: and qph.source_system_code = qprs.source_system_code
2587: and ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') = 'N')
2588: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
2589: union all
2590: select /*+ ORDERED USE_NL(qpq qplh qph) index(qpq qp_qualifiers_n6) index(qph qp_list_headers_b_n7) index(qplatq qp_preq_line_attrs_tmp_n2) dynamic_sampling(1) l_header_outer_qual_cur_mod */
2591: qpq.list_header_id,
2592: qpq.list_line_id list_line_id,
2593: qpq.qualifier_group_cnt,
2594: qpq.others_group_cnt,
2691: and qph.source_system_code = qprs.source_system_code
2692: and ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') = 'N')
2693: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
2694: union all
2695: select /*+ ORDERED USE_NL(qpq qplh qph) index(qpq qp_qualifiers_n5) index(qph qp_list_headers_b_n7) index(qplatq qp_preq_line_attrs_tmp_n2) dynamic_sampling(1) l_header_outer_qual_cur_mod */
2696: qpq.list_header_id,
2697: qpq.list_line_id list_line_id,
2698: qpq.qualifier_group_cnt,
2699: qpq.others_group_cnt,
2798: and qph.source_system_code = qprs.source_system_code
2799: and ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') = 'N')
2800: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
2801: union all
2802: select /*+ ORDERED USE_NL(qpq qplh qph) index(qpq qp_qualifiers_n5) index(qph qp_list_headers_b_n7) index(qplatq qp_preq_line_attrs_tmp_n2) dynamic_sampling(1) l_header_outer_qual_cur_mod */
2803: qpq.list_header_id,
2804: qpq.list_line_id list_line_id,
2805: qpq.qualifier_group_cnt,
2806: qpq.others_group_cnt,
3503: INDX,QP_PREQ_GRP.set_status_code.set_status_code_upd3,qp_npreq_line_attrs_tmp_N1,LINE_INDEX,1
3504: INDX,QP_PREQ_GRP.set_status_code.set_status_code_upd3,qp_npreq_line_attrs_tmp_N1,PRICING_STATUS_CODE,3
3505: */
3506: --bug 14464004
3507: UPDATE /*+ dynamic_sampling(1) index(qp_npreq_line_attrs_tmp qp_preq_line_attrs_tmp_n1) */ qp_npreq_line_attrs_tmp --upd3
3508: SET PRICING_STATUS_CODE = p_status_code,
3509: PRICING_STATUS_TEXT = substr(p_status_text,1,240)
3510: WHERE LINE_INDEX = p_line_index
3511: AND PRICING_STATUS_CODE <> G_STATUS_UNCHANGED;
7868:
7869: -- julin [3805113]: bulk update for CROSS ORDER VOLUME
7870: IF (l_co_line_index_tbl.count > 0) THEN
7871: FORALL I IN l_co_line_index_tbl.first..l_co_line_index_tbl.last
7872: UPDATE /*+ index(qp_npreq_line_attrs_tmp qp_preq_line_attrs_tmp_n2) */ qp_npreq_line_attrs_tmp --upd4
7873: SET VALUE_FROM = qp_number.number_to_canonical(round(qp_number.canonical_to_number(nvl(VALUE_FROM,0)) * v_uom_rate, G_INV_DECIMAL_PRECISION))
7874: WHERE LINE_INDEX = l_co_line_index_tbl(I)
7875: AND CONTEXT = G_PRIC_VOLUME_CONTEXT
7876: AND ATTRIBUTE IN ('PRICING_ATTRIBUTE3','PRICING_ATTRIBUTE1','PRICING_ATTRIBUTE11')
7880:
7881: -- julin [3805113]: bulk update for ITEM AMOUNT
7882: IF (l_ia_line_index_tbl.count > 0) THEN
7883: FORALL I IN l_ia_line_index_tbl.first..l_ia_line_index_tbl.last
7884: UPDATE /*+ index(qp_npreq_line_attrs_tmp qp_preq_line_attrs_tmp_n2) */ qp_npreq_line_attrs_tmp --upd3, upd5
7885: SET VALUE_FROM = l_ia_value_from_tbl(I)
7886: WHERE LINE_INDEX = l_ia_line_index_tbl(I)
7887: AND CONTEXT = G_PRIC_VOLUME_CONTEXT
7888: AND ATTRIBUTE = l_ia_attribute_tbl(I)
8000: UPDATE /*+ dynamic_sampling(1) */ qp_npreq_line_attrs_tmp a
8001: SET distinct_qualifier_flag = 'Y'
8002: WHERE pricing_status_code = G_STATUS_UNCHANGED
8003: AND attribute_type = G_QUALIFIER_TYPE
8004: AND rowid = (SELECT /*+ dynamic_sampling(1) INDEX(b QP_PREQ_LINE_ATTRS_TMP_N6) */ min(rowid) --bug 14464004
8005: FROM qp_npreq_line_attrs_tmp b
8006: WHERE a.context = b.context
8007: AND a.attribute = b.attribute
8008: AND a.value_from = b.value_from
8113: AND list_line_id = -1
8114: AND NOT EXISTS (select /*+ NO_UNNEST
8115: leading (c)
8116: use_nl (c qpprod)
8117: index (qpprod, QP_PREQ_LINE_ATTRS_TMP_N6)
8118: index(c qp_pricing_attributes_n13) */
8119: 'x'
8120: FROM qp_npreq_line_attrs_tmp qpprod, qp_pricing_attributes c
8121: WHERE c.list_header_id = a.list_header_id -- delete header for which product does
8132: --UNION
8133: --SELECT /*+ ORDERED USE_NL(d qppric) index(d qp_pricing_attributes_n5) */ 'x'
8134: /*FROM qp_npreq_line_attrs_tmp qpprod,
8135: qp_pricing_attributes d,
8136: qp_preq_line_attrs_tmp qppric
8137: WHERE d.list_header_id = a.list_header_id -- delete header for which product and
8138: AND d.pricing_phase_id = p_pricing_phase_id -- pricing attrbutes do not match
8139: AND d.QUALIFICATION_IND in (G_YES_PROD_PRIC_HDR_QUAL_IND, G_YES_PRIC_HDR_LINE_QUAL_IND)
8140: AND d.product_attribute_context = qpprod.context
8216: WHERE a.ASK_FOR_FLAG = QP_PREQ_GRP.G_YES
8217: AND a.PRICING_PHASE_ID = p_pricing_phase_id
8218: AND a.LINE_INDEX = p_line_index
8219: AND NOT EXISTS (
8220: SELECT /*+ index(b QP_PREQ_LINE_ATTRS_TMP_N2) */ 'X' -- This can use N2 instead of N7
8221: FROM qp_npreq_line_attrs_tmp b
8222: WHERE b.LINE_INDEX = p_line_index
8223: AND b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_UNCHANGED
8224: AND b.ATTRIBUTE_TYPE = QP_PREQ_GRP.G_QUALIFIER_TYPE
9492: INDX,QP_PREQ_GRP.populate_output.l_pricing_attr_cur,qp_npreq_line_attrs_tmp_N3,LINE_DETAIL_INDEX,1
9493: INDX,QP_PREQ_GRP.populate_output.l_pricing_attr_cur,qp_npreq_line_attrs_tmp_N3,ATTRIBUTE_TYPE,2
9494: */
9495: CURSOR l_pricing_attr_cur IS
9496: SELECT /*+ dynamic_sampling(1) index(QPLAT_PRICING QP_PREQ_LINE_ATTRS_TMP_N3) */ --bug8359591
9497: QPLAT_PRICING.CONTEXT PRICING_CONTEXT,
9498: QPLAT_PRICING.ATTRIBUTE PRICING_ATTRIBUTE,
9499: nvl(QPLAT_PRICING.SETUP_VALUE_FROM,QPLAT_PRICING.VALUE_FROM) PRICING_ATTR_VALUE_FROM,
9500: QPLAT_PRICING.SETUP_VALUE_TO PRICING_ATTR_VALUE_TO,
10375: */
10376:
10377: --Pricing Phase Change
10378: CURSOR l_get_multiple_exclude_cur IS
10379: SELECT /*+ ORDERED USE_NL(b) index(a qp_preq_line_attrs_tmp_N5) l_get_multiple_exclude_cur */
10380: distinct a.line_index , a.list_header_id , a.list_line_id , a.grouping_number grouping_no,
10381: a.context , a.attribute , a.setup_value_from
10382: FROM qp_npreq_line_attrs_tmp a , qp_npreq_ldets_tmp b
10383: WHERE
11057:
11058: */
11059:
11060: CURSOR l_pric_between_cur(p_datatype VARCHAR2) IS
11061: SELECT /*+ ORDERED USE_NL(ldets) index(pricing qp_preq_line_attrs_tmp_N5) l_pric_between_cur */
11062: pricing.line_detail_index,
11063: pricing.line_index,
11064: pricing.value_from,
11065: -- line group not applicable for price lists.ignore any code around line group for price lists
11094:
11095: */
11096:
11097: CURSOR l_mod_between_cur(p_datatype VARCHAR2) IS
11098: SELECT /*+ ORDERED USE_NL(ldets) index(pricing qp_preq_line_attrs_tmp_N5) l_mod_between_cur */
11099: pricing.line_detail_index,
11100: pricing.line_index,
11101: pricing.value_from,
11102: ldets.group_quantity,
11130:
11131: */
11132:
11133: CURSOR l_pric_character_between_cur IS
11134: SELECT /*+ ORDERED USE_NL(c) index(b qp_preq_line_attrs_tmp_N5) l_pric_character_between_cur */
11135: b.line_detail_index,
11136: b.line_index,
11137: b.value_from,
11138: b.setup_value_from,
11165:
11166: */
11167:
11168: CURSOR l_mod_character_between_cur IS
11169: SELECT /*+ ORDERED USE_NL(c) index(b qp_preq_line_attrs_tmp_N5) l_mod_character_between_cur */
11170: b.line_detail_index,
11171: b.line_index,
11172: b.value_from,
11173: b.setup_value_from,
11794: AND NVL(TRUNC(QCDT.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
11795: AND ( /* Vivek */
11796: (qcdt.curr_attribute_context is null /* Vivek */
11797: and not exists /* Vivek */
11798: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
11799: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1 /* Vivek */
11800: where qcdt1.curr_attribute_context IS NOT NULL /* Vivek */
11801: and qcdt1.curr_attribute_context = pa_tmp.context /* Vivek */
11802: and qcdt1.curr_attribute = pa_tmp.attribute /* Vivek */
11813: ) /* Vivek */
11814: OR /* Vivek */
11815: (qcdt.curr_attribute_context is not null /* Vivek */
11816: and qcdt.precedence =
11817: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
11818: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2 /* Vivek */
11819: where qcdt2.curr_attribute_context IS NOT NULL /* Vivek */
11820: and qcdt2.curr_attribute_context = pa_tmp1.context /* Vivek */
11821: and qcdt2.curr_attribute = pa_tmp1.attribute /* Vivek */
13341: */
13342:
13343: --Pricing Phase Change
13344: CURSOR header_level_qual_attrs_cur IS
13345: SELECT /*+ ORDERED USE_NL(b) index(a qp_preq_line_attrs_tmp_N5) header_level_qual_attrs_cur */
13346: a.LIST_HEADER_ID,
13347: a.LIST_LINE_ID,
13348: a.LINE_INDEX,
13349: a.GROUPING_NUMBER,
13370:
13371: */
13372:
13373: CURSOR line_level_qual_attrs_cur2 IS
13374: SELECT /*+ ORDERED USE_NL(b) index(a qp_preq_line_attrs_tmp_N5) line_level_qual_attrs_cur2 */
13375: a.LIST_HEADER_ID,
13376: a.LIST_LINE_ID,
13377: a.LINE_INDEX,
13378: a.GROUPING_NUMBER,
13589:
13590: */
13591: --Pricing Phase Change
13592: CURSOR product_level_attrs_cur IS
13593: SELECT /*+ ORDERED USE_NL(b) index(a qp_preq_line_attrs_tmp_N5) index(b QP_PREQ_LDETS_TMP_U1) product_level_attrs_cur */ --5658579
13594: a.LIST_HEADER_ID , a.LIST_LINE_ID , a.LINE_INDEX,
13595: SUM(distinct(decode(a.attribute_type,G_PRODUCT_TYPE,1,0))) +
13596: COUNT(distinct(decode(a.attribute_type,G_PRICING_TYPE, a.context || ' ' || a.attribute || ' ' || setup_value_from ,NULL)))
13597: NO_OF_PATTS_IN_GRP -- Bug No: 7129919
13742: p_list_line_id PLS_INTEGER) IS
13743: SELECT 1
13744: FROM DUAL
13745: WHERE EXISTS
13746: (SELECT /*+ index(QP_PREQ_LINE_ATTRS_TMP QP_PREQ_LINE_ATTRS_TMP_N10) */ 'x' --9679930
13747: FROM QP_PREQ_LINE_ATTRS_TMP
13748: WHERE LINE_INDEX = p_line_index
13749: AND LIST_HEADER_ID = p_list_header_id
13750: AND LIST_LINE_ID = p_list_line_id
13743: SELECT 1
13744: FROM DUAL
13745: WHERE EXISTS
13746: (SELECT /*+ index(QP_PREQ_LINE_ATTRS_TMP QP_PREQ_LINE_ATTRS_TMP_N10) */ 'x' --9679930
13747: FROM QP_PREQ_LINE_ATTRS_TMP
13748: WHERE LINE_INDEX = p_line_index
13749: AND LIST_HEADER_ID = p_list_header_id
13750: AND LIST_LINE_ID = p_list_line_id
13751: AND PRICING_STATUS_CODE = G_DELETED_BETWEEN
14644: --[julin/pricebook] added hint
14645: --[julin/5223502] price_list_header_id directly passed in via lines_tmp has precedence over sourced value
14646: UPDATE /*+ dynamic_sampling(1) */ qp_npreq_lines_tmp a
14647: SET (price_list_header_id,validated_flag) =
14648: (SELECT /*+ index(b QP_PREQ_LINE_ATTRS_TMP_N2) */ distinct
14649: CASE WHEN (a.PRICE_LIST_HEADER_ID < 0) THEN qp_number.canonical_to_number(value_from)
14650: ELSE a.PRICE_LIST_HEADER_ID
14651: END price_list_header_id,
14652: validated_flag
15007: AND QPLAT_PROD.PRICING_STATUS_CODE = G_STATUS_UNCHANGED
15008: AND QPLINES.PROCESSED_CODE = G_STS_LHS_NOT_FOUND
15009: AND (instr(QPLINES.PROCESS_STATUS,'FREEGOOD')=0) -- bug 2693700
15010: UNION ALL
15011: SELECT /*+ ORDERED USE_NL(QPQ QPLAT_PROD QPA QPLAT_PRIC QPL QPH) index(QPA QP_PRICING_ATTRIBUTES_N5) index(qph qp_list_headers_b_n2) index(qplat_pric qp_preq_line_attrs_tmp_n2) l_sec_list_cur */
15012: QPLAT_PROD.LINE_INDEX,
15013: QPL.LIST_LINE_ID,
15014: QPL.ARITHMETIC_OPERATOR,
15015: QPL.OPERAND,
15275: AND G_QUALIFY_SECONDARY = G_YES
15276: AND ((nvl(QPLAT_Q.DERIVED_QUALIFIER_FLAG,'N')='N')
15277: OR (QPLAT_Q.DERIVED_QUALIFIER_FLAG = 'Y' AND QPSQ.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
15278: UNION ALL
15279: SELECT /*+ ORDERED USE_NL(QPQ QPLAT_PROD QPA QPLAT_PRIC QPL QPH) index(QPA QP_PRICING_ATTRIBUTES_N5) index(qph qp_list_headers_b_n2) index(qplat_pric qp_preq_line_attrs_tmp_n2) l_sec_list_cur */
15280: QPLAT_PROD.LINE_INDEX,
15281: QPL.LIST_LINE_ID,
15282: QPL.ARITHMETIC_OPERATOR,
15283: QPL.OPERAND,
15546: AND NVL(TRUNC(QCDT.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
15547: AND ( /* Vivek */
15548: (qcdt.curr_attribute_context is null /* Vivek */
15549: and not exists /* Vivek */
15550: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
15551: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1 /* Vivek */
15552: where qcdt1.curr_attribute_context IS NOT NULL /* Vivek */
15553: and qcdt1.curr_attribute_context = pa_tmp.context /* Vivek */
15554: and qcdt1.curr_attribute = pa_tmp.attribute /* Vivek */
15573: AND QPLINES.PRICING_EFFECTIVE_DATE BETWEEN /* Vivek */
15574: NVL(TRUNC(QCDT3.START_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
15575: AND NVL(TRUNC(QCDT3.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
15576: and qcdt3.precedence =
15577: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
15578: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2 /* Vivek */
15579: where qcdt2.curr_attribute_context IS NOT NULL /* Vivek */
15580: and qcdt2.curr_attribute_context = pa_tmp1.context /* Vivek */
15581: and qcdt2.curr_attribute = pa_tmp1.attribute /* Vivek */
15607: AND QPLAT_PROD.PRICING_STATUS_CODE = G_STATUS_UNCHANGED
15608: AND QPLINES.PROCESSED_CODE = G_STS_LHS_NOT_FOUND
15609: AND (instr(QPLINES.PROCESS_STATUS,'FREEGOOD')=0) -- bug 2693700
15610: UNION ALL
15611: SELECT /*+ ORDERED USE_NL(QPQ QPLAT_PROD QPA QPLAT_PRIC QPL QPH) index(QPA QP_PRICING_ATTRIBUTES_N5) index(qph qp_list_headers_b_n7) index(qplat_pric qp_preq_line_attrs_tmp_n2) l_sec_list_cur */
15612: QPLAT_PROD.LINE_INDEX,
15613: QPL.LIST_LINE_ID,
15614: QPL.ARITHMETIC_OPERATOR,
15615: QPL.OPERAND,
15737: AND NVL(TRUNC(QCDT.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
15738: AND ( /* Vivek */
15739: (qcdt.curr_attribute_context is null /* Vivek */
15740: and not exists /* Vivek */
15741: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
15742: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1 /* Vivek */
15743: where qcdt1.curr_attribute_context IS NOT NULL /* Vivek */
15744: and qcdt1.curr_attribute_context = pa_tmp.context /* Vivek */
15745: and qcdt1.curr_attribute = pa_tmp.attribute /* Vivek */
15764: AND QPLINES.PRICING_EFFECTIVE_DATE BETWEEN /* Vivek */
15765: NVL(TRUNC(QCDT3.START_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
15766: AND NVL(TRUNC(QCDT3.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
15767: and qcdt3.precedence =
15768: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
15769: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2 /* Vivek */
15770: where qcdt2.curr_attribute_context IS NOT NULL /* Vivek */
15771: and qcdt2.curr_attribute_context = pa_tmp1.context /* Vivek */
15772: and qcdt2.curr_attribute = pa_tmp1.attribute /* Vivek */
15911: AND NVL(TRUNC(QCDT.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
15912: AND ( /* Vivek */
15913: (qcdt.curr_attribute_context is null /* Vivek */
15914: and not exists /* Vivek */
15915: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
15916: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1 /* Vivek */
15917: where qcdt1.curr_attribute_context IS NOT NULL /* Vivek */
15918: and qcdt1.curr_attribute_context = pa_tmp.context /* Vivek */
15919: and qcdt1.curr_attribute = pa_tmp.attribute /* Vivek */
15938: AND QPLINES.PRICING_EFFECTIVE_DATE BETWEEN /* Vivek */
15939: NVL(TRUNC(QCDT3.START_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
15940: AND NVL(TRUNC(QCDT3.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
15941: and qcdt3.precedence =
15942: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
15943: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2 /* Vivek */
15944: where qcdt2.curr_attribute_context IS NOT NULL /* Vivek */
15945: and qcdt2.curr_attribute_context = pa_tmp1.context /* Vivek */
15946: and qcdt2.curr_attribute = pa_tmp1.attribute /* Vivek */
15995: AND G_QUALIFY_SECONDARY = G_YES
15996: AND ((nvl(QPLAT_Q.DERIVED_QUALIFIER_FLAG,'N')='N')
15997: OR (QPLAT_Q.DERIVED_QUALIFIER_FLAG = 'Y' AND QPSQ.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
15998: UNION ALL
15999: SELECT /*+ ORDERED USE_NL(QPQ QPLAT_PROD QPA QPLAT_PRIC QPL QPH) index(QPA QP_PRICING_ATTRIBUTES_N5) index(qph qp_list_headers_b_n7) index(qplat_pric qp_preq_line_attrs_tmp_n2) l_sec_list_cur */
16000: QPLAT_PROD.LINE_INDEX,
16001: QPL.LIST_LINE_ID,
16002: QPL.ARITHMETIC_OPERATOR,
16003: QPL.OPERAND,
16122: AND NVL(TRUNC(QCDT.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
16123: AND ( /* Vivek */
16124: (qcdt.curr_attribute_context is null /* Vivek */
16125: and not exists /* Vivek */
16126: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
16127: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1 /* Vivek */
16128: where qcdt1.curr_attribute_context IS NOT NULL /* Vivek */
16129: and qcdt1.curr_attribute_context = pa_tmp.context /* Vivek */
16130: and qcdt1.curr_attribute = pa_tmp.attribute /* Vivek */
16149: AND QPLINES.PRICING_EFFECTIVE_DATE BETWEEN /* Vivek */
16150: NVL(TRUNC(QCDT3.START_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
16151: AND NVL(TRUNC(QCDT3.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
16152: and qcdt3.precedence =
16153: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
16154: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2 /* Vivek */
16155: where qcdt2.curr_attribute_context IS NOT NULL /* Vivek */
16156: and qcdt2.curr_attribute_context = pa_tmp1.context /* Vivek */
16157: and qcdt2.curr_attribute = pa_tmp1.attribute /* Vivek */
17223: AND QPLAT_PROD.LINE_INDEX = QPLINES.LINE_INDEX
17224: AND QPL.PRICING_PHASE_ID = G_PRICE_LIST_PHASE_ID
17225: AND (instr(QPLINES.PROCESS_STATUS,'FREEGOOD')=0) -- bug 2693700
17226: UNION ALL
17227: SELECT /*+ ORDERED USE_NL(QPLAT_PROD QPA QPLAT_PRIC QPL QPH) index(QPA QP_PRICING_ATTRIBUTES_N5) index(qplat_pric qp_preq_line_attrs_tmp_N2) dynamic_sampling(1) l_list_cur */
17228: QPLAT_PROD.LINE_INDEX,
17229: QPL.LIST_LINE_ID,
17230: QPL.ARITHMETIC_OPERATOR,
17231: QPL.OPERAND,
17342:
17343: -- vivek
17344: CURSOR L_LIST_CUR_CURRENCY IS
17345: SELECT /*+ ORDERED USE_NL(QPLAT_PROD QPA QPL QPH) index(QPA QP_PRICING_ATTRIBUTES_N5)
17346: index(qplat_prod qp_preq_line_attrs_tmp_n1) index(QPLINES QP_PREQ_LINES_TMP_N2) l_list_cur */ -- 9362867
17347: QPLAT_PROD.LINE_INDEX,
17348: QPL.LIST_LINE_ID,
17349: QPL.ARITHMETIC_OPERATOR,
17350: QPL.OPERAND,
17452: AND NVL(TRUNC(QCDT.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
17453: AND ( /* Vivek */
17454: (qcdt.curr_attribute_context is null /* Vivek */
17455: and not exists /* Vivek */
17456: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
17457: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1 /* Vivek */
17458: where qcdt1.curr_attribute_context IS NOT NULL /* Vivek */
17459: and qcdt1.curr_attribute_context = pa_tmp.context /* Vivek */
17460: and qcdt1.curr_attribute = pa_tmp.attribute /* Vivek */
17471: ) /* Vivek */
17472: OR /* Vivek */
17473: (qcdt.curr_attribute_context is not null /* Vivek */
17474: and qcdt.precedence =
17475: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
17476: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2 /* Vivek */
17477: where qcdt2.curr_attribute_context IS NOT NULL /* Vivek */
17478: and qcdt2.curr_attribute_context = pa_tmp1.context /* Vivek */
17479: and qcdt2.curr_attribute = pa_tmp1.attribute /* Vivek */
17499: AND QPLAT_PROD.LINE_INDEX = QPLINES.LINE_INDEX
17500: AND QPL.PRICING_PHASE_ID = G_PRICE_LIST_PHASE_ID
17501: AND (instr(QPLINES.PROCESS_STATUS,'FREEGOOD')=0) -- bug 2693700
17502: UNION ALL
17503: SELECT /*+ ORDERED USE_NL(QPLAT_PROD QPA QPLAT_PRIC QPL QPH) index(QPA QP_PRICING_ATTRIBUTES_N5) index(qplat_pric qp_preq_line_attrs_tmp_N2) l_list_cur */
17504: QPLAT_PROD.LINE_INDEX,
17505: QPL.LIST_LINE_ID,
17506: QPL.ARITHMETIC_OPERATOR,
17507: QPL.OPERAND,
17619: AND NVL(TRUNC(QCDT.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
17620: AND ( /* Vivek */
17621: (qcdt.curr_attribute_context is null /* Vivek */
17622: and not exists /* Vivek */
17623: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
17624: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1 /* Vivek */
17625: where qcdt1.curr_attribute_context IS NOT NULL /* Vivek */
17626: and qcdt1.curr_attribute_context = pa_tmp.context /* Vivek */
17627: and qcdt1.curr_attribute = pa_tmp.attribute /* Vivek */
17638: ) /* Vivek */
17639: OR /* Vivek */
17640: (qcdt.curr_attribute_context is not null /* Vivek */
17641: and qcdt.precedence =
17642: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
17643: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2 /* Vivek */
17644: where qcdt2.curr_attribute_context IS NOT NULL /* Vivek */
17645: and qcdt2.curr_attribute_context = pa_tmp1.context /* Vivek */
17646: and qcdt2.curr_attribute = pa_tmp1.attribute /* Vivek */
17710: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
17711: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
17712: */
17713: CURSOR L_LIST_QUAL_CUR IS
17714: SELECT /*+ ORDERED USE_NL(QPQ QPLAT_Q QPLAT_PROD QPA QPL QPH) INDEX(QPQ QP_QUALIFIERS_N1) INDEX(QPLAT_Q qp_preq_line_attrs_tmp_N2) INDEX(QPA QP_PRICING_ATTRIBUTES_N5) dynamic_sampling(1) l_list_qual_cur */
17715: QPLAT_PROD.LINE_INDEX,
17716: QPL.LIST_LINE_ID,
17717: QPL.ARITHMETIC_OPERATOR,
17718: QPL.OPERAND,
17836: AND (instr(QPLINES.PROCESS_STATUS,'FREEGOOD')=0) -- bug 2693700
17837: AND ((nvl(QPLAT_Q.DERIVED_QUALIFIER_FLAG,'N')='N')
17838: OR (QPLAT_Q.DERIVED_QUALIFIER_FLAG = 'Y' AND QPQ.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
17839: UNION ALL
17840: SELECT /*+ ORDERED USE_NL(QPLINES QPQ QPLAT_Q QPLAT_PROD QPA QPLAT_PRIC QPL QPH) INDEX(QPQ QP_QUALIFIERS_N1) INDEX(QPLAT_Q qp_preq_line_attrs_tmp_N2)
17841: INDEX(QPA QP_PRICING_ATTRIBUTES_N5) INDEX(QPLAT_PRIC qp_preq_line_attrs_tmp_N2) dynamic_sampling(1) l_list_qual_cur */
17842: QPLAT_PROD.LINE_INDEX,
17843: QPL.LIST_LINE_ID,
17844: QPL.ARITHMETIC_OPERATOR,
17837: AND ((nvl(QPLAT_Q.DERIVED_QUALIFIER_FLAG,'N')='N')
17838: OR (QPLAT_Q.DERIVED_QUALIFIER_FLAG = 'Y' AND QPQ.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
17839: UNION ALL
17840: SELECT /*+ ORDERED USE_NL(QPLINES QPQ QPLAT_Q QPLAT_PROD QPA QPLAT_PRIC QPL QPH) INDEX(QPQ QP_QUALIFIERS_N1) INDEX(QPLAT_Q qp_preq_line_attrs_tmp_N2)
17841: INDEX(QPA QP_PRICING_ATTRIBUTES_N5) INDEX(QPLAT_PRIC qp_preq_line_attrs_tmp_N2) dynamic_sampling(1) l_list_qual_cur */
17842: QPLAT_PROD.LINE_INDEX,
17843: QPL.LIST_LINE_ID,
17844: QPL.ARITHMETIC_OPERATOR,
17845: QPL.OPERAND,
18075: AND QPLAT_PROD.LINE_INDEX = QPLINES.LINE_INDEX
18076: --AND QPL.PRICING_PHASE_ID = G_PRICE_LIST_PHASE_ID
18077: AND (instr(QPLINES.PROCESS_STATUS,'FREEGOOD')=0) -- bug 2693700
18078: UNION ALL
18079: SELECT /*+ ORDERED USE_NL(QPLINES QPLAT_PROD QPA QPLAT_PRIC QPL QPH) index(QPA QP_PRICING_ATTRIBUTES_N5) INDEX(QPLAT_PRIC qp_preq_line_attrs_tmp_N2) dynamic_sampling(1) l_list_cur */
18080: QPLAT_PROD.LINE_INDEX,
18081: QPL.LIST_LINE_ID,
18082: QPL.ARITHMETIC_OPERATOR,
18083: QPL.OPERAND,
18189: ORDER BY 1,2;
18190:
18191: -- vivek
18192: CURSOR L_LIST_QUAL_CUR_CURRENCY IS
18193: SELECT /*+ ORDERED USE_NL(QPQ QPLAT_Q QPLAT_PROD QPA QPL QPH) INDEX(QPQ QP_QUALIFIERS_N1) INDEX(QPLAT_Q qp_preq_line_attrs_tmp_N2) l_list_qual_cur */
18194: QPLAT_PROD.LINE_INDEX,
18195: QPL.LIST_LINE_ID,
18196: QPL.ARITHMETIC_OPERATOR,
18197: QPL.OPERAND,
18297: AND NVL(TRUNC(QCDT.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
18298: AND ( /* Vivek */
18299: (qcdt.curr_attribute_context is null /* Vivek */
18300: and not exists /* Vivek */
18301: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
18302: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1 /* Vivek */
18303: where qcdt1.curr_attribute_context IS NOT NULL /* Vivek */
18304: and qcdt1.curr_attribute_context = pa_tmp.context /* Vivek */
18305: and qcdt1.curr_attribute = pa_tmp.attribute /* Vivek */
18324: AND QPLINES.PRICING_EFFECTIVE_DATE BETWEEN /* Vivek */
18325: NVL(TRUNC(QCDT3.START_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
18326: AND NVL(TRUNC(QCDT3.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
18327: and qcdt3.precedence =
18328: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
18329: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2 /* Vivek */
18330: where qcdt2.curr_attribute_context IS NOT NULL /* Vivek */
18331: and qcdt2.curr_attribute_context = pa_tmp1.context /* Vivek */
18332: and qcdt2.curr_attribute = pa_tmp1.attribute /* Vivek */
18374: AND (instr(QPLINES.PROCESS_STATUS,'FREEGOOD')=0) -- bug 2693700
18375: AND ((nvl(QPLAT_Q.DERIVED_QUALIFIER_FLAG,'N')='N')
18376: OR (QPLAT_Q.DERIVED_QUALIFIER_FLAG = 'Y' AND QPQ.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
18377: UNION ALL
18378: SELECT /*+ ORDERED USE_NL(QPLINES QPQ QPLAT_Q QPLAT_PROD QPA QPLAT_PRIC QPL QPH) INDEX(QPQ QP_QUALIFIERS_N1) INDEX(QPLAT_Q qp_preq_line_attrs_tmp_N2) INDEX(QPA QP_PRICING_ATTRIBUTES_N5) INDEX(QPLAT_PRIC qp_preq_line_attrs_tmp_N2) l_list_qual_cur */
18379: QPLAT_PROD.LINE_INDEX,
18380: QPL.LIST_LINE_ID,
18381: QPL.ARITHMETIC_OPERATOR,
18382: QPL.OPERAND,
18486: AND NVL(TRUNC(QCDT.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
18487: AND ( /* Vivek */
18488: (qcdt.curr_attribute_context is null /* Vivek */
18489: and not exists /* Vivek */
18490: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
18491: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1 /* Vivek */
18492: where qcdt1.curr_attribute_context IS NOT NULL /* Vivek */
18493: and qcdt1.curr_attribute_context = pa_tmp.context /* Vivek */
18494: and qcdt1.curr_attribute = pa_tmp.attribute /* Vivek */
18513: AND QPLINES.PRICING_EFFECTIVE_DATE BETWEEN /* Vivek */
18514: NVL(TRUNC(QCDT3.START_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
18515: AND NVL(TRUNC(QCDT3.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
18516: and qcdt3.precedence =
18517: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
18518: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2 /* Vivek */
18519: where qcdt2.curr_attribute_context IS NOT NULL /* Vivek */
18520: and qcdt2.curr_attribute_context = pa_tmp1.context /* Vivek */
18521: and qcdt2.curr_attribute = pa_tmp1.attribute /* Vivek */
18676: AND NVL(TRUNC(QCDT.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
18677: AND ( /* Vivek */
18678: (qcdt.curr_attribute_context is null /* Vivek */
18679: and not exists /* Vivek */
18680: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
18681: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1 /* Vivek */
18682: where qcdt1.curr_attribute_context IS NOT NULL /* Vivek */
18683: and qcdt1.curr_attribute_context = pa_tmp.context /* Vivek */
18684: and qcdt1.curr_attribute = pa_tmp.attribute /* Vivek */
18703: AND QPLINES.PRICING_EFFECTIVE_DATE BETWEEN /* Vivek */
18704: NVL(TRUNC(QCDT3.START_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
18705: AND NVL(TRUNC(QCDT3.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
18706: and qcdt3.precedence =
18707: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
18708: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2 /* Vivek */
18709: where qcdt2.curr_attribute_context IS NOT NULL /* Vivek */
18710: and qcdt2.curr_attribute_context = pa_tmp1.context /* Vivek */
18711: and qcdt2.curr_attribute = pa_tmp1.attribute /* Vivek */
18731: AND QPLAT_PROD.LINE_INDEX = QPLINES.LINE_INDEX
18732: --AND QPL.PRICING_PHASE_ID = G_PRICE_LIST_PHASE_ID
18733: AND (instr(QPLINES.PROCESS_STATUS,'FREEGOOD')=0) -- bug 2693700
18734: UNION ALL
18735: SELECT /*+ ORDERED USE_NL(QPLINES QPLAT_PROD QPA QPLAT_PRIC QPL QPH) index(QPA QP_PRICING_ATTRIBUTES_N5) INDEX(QPLAT_PRIC qp_preq_line_attrs_tmp_N2) l_list_cur */
18736: QPLAT_PROD.LINE_INDEX,
18737: QPL.LIST_LINE_ID,
18738: QPL.ARITHMETIC_OPERATOR,
18739: QPL.OPERAND,
18847: AND NVL(TRUNC(QCDT.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
18848: AND ( /* Vivek */
18849: (qcdt.curr_attribute_context is null /* Vivek */
18850: and not exists /* Vivek */
18851: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
18852: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1 /* Vivek */
18853: where qcdt1.curr_attribute_context IS NOT NULL /* Vivek */
18854: and qcdt1.curr_attribute_context = pa_tmp.context /* Vivek */
18855: and qcdt1.curr_attribute = pa_tmp.attribute /* Vivek */
18874: AND QPLINES.PRICING_EFFECTIVE_DATE BETWEEN /* Vivek */
18875: NVL(TRUNC(QCDT3.START_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
18876: AND NVL(TRUNC(QCDT3.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
18877: and qcdt3.precedence =
18878: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
18879: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2 /* Vivek */
18880: where qcdt2.curr_attribute_context IS NOT NULL /* Vivek */
18881: and qcdt2.curr_attribute_context = pa_tmp1.context /* Vivek */
18882: and qcdt2.curr_attribute = pa_tmp1.attribute /* Vivek */
19092: AND QPLINES.REQUEST_TYPE_CODE = QPRS.REQUEST_TYPE_CODE
19093: AND QPH.ACTIVE_FLAG = G_YES
19094: AND QPLAT_PROD.PRICING_STATUS_CODE = G_STATUS_UNCHANGED
19095: UNION ALL
19096: SELECT /*+ ORDERED USE_NL(QPLAT_PROD QPA QPLAT_PRIC QPH QPL QPRS) index(QPA QP_PRICING_ATTRIBUTES_N5) index(qplat_pric qp_preq_line_attrs_tmp_N2) index(qph qp_list_headers_b_n7) l_other_list_cur */
19097: QPLINES.LINE_INDEX,
19098: QPL.LIST_LINE_ID,
19099: QPL.ARITHMETIC_OPERATOR,
19100: QPL.OPERAND,
19340: AND QPLINES.REQUEST_TYPE_CODE = QPRS.REQUEST_TYPE_CODE
19341: AND QPH.ACTIVE_FLAG = G_YES
19342: AND QPLAT_PROD.PRICING_STATUS_CODE = G_STATUS_UNCHANGED
19343: UNION ALL
19344: SELECT /*+ ORDERED USE_NL(QPLATP_PROD QPA QPLAT_PRIC QPH QPL QPRS) index(QPA QP_PRICING_ATTRIBUTES_N5) index(qplat_pric qp_preq_line_attrs_tmp_N2) index(qph qp_list_headers_b_n7) l_other_list_line_cur */
19345: QPLINES.LINE_INDEX,
19346: QPL.LIST_LINE_ID,
19347: QPL.ARITHMETIC_OPERATOR,
19348: QPL.OPERAND,
25487: */
25488:
25489: CURSOR l_prod_qual_cur (p_pricing_phase_id NUMBER, p_list_header_id NUMBER, p_list_line_id NUMBER, p_line_index NUMBER) IS
25490: -- Product and Qualifier Only. No pricing
25491: select /*+ ORDERED USE_NL(QPPROD QPLATPR QPL ) index(QPPROD QP_PRICING_ATTRIBUTES_N7) index(qplatpr qp_preq_line_attrs_tmp_n2) l_prod_qual_cur_mod */
25492: qpl.list_header_id,
25493: qpl.list_line_id,
25494: qpl.list_line_type_code,
25495: qplatpr.line_index,
25578: G_COUPON_ISSUE,G_TERMS_SUBSTITUTION,G_ITEM_UPGRADE)
25579: or
25580: nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) <> G_YES)
25581: union all
25582: select /*+ ORDERED USE_NL(QPLATPR QPPROD QPL) index(QPPROD QP_PRICING_ATTRIBUTES_N5) index(qplatpr qp_preq_line_attrs_tmp_n1) l_prod_qual_cur_mod */
25583: qpl.list_header_id,
25584: qpl.list_line_id,
25585: qpl.list_line_type_code,
25586: qplatpr.line_index,
25722: */
25723: CURSOR l_prod_qual_pric_cur(p_pricing_phase_id NUMBER, p_list_header_id NUMBER, p_list_line_id NUMBER,
25724: p_line_index NUMBER) IS
25725: --Product+Qualifiers+Pricing
25726: select /*+ ORDERED USE_NL(QPPROD QPLATPR QPLATPC QPL ) index(QPPROD QP_PRICING_ATTRIBUTES_N7) index(qplatpr qp_preq_line_attrs_tmp_N2) index(qplatpc qp_preq_line_attrs_tmp_N2) l_prod_qual_pric_cur_mod */
25727: qpl.list_header_id,
25728: qpl.list_line_id,
25729: qpl.list_line_type_code,
25730: qplatpr.line_index,
25837: G_COUPON_ISSUE,G_TERMS_SUBSTITUTION,G_ITEM_UPGRADE)
25838: or
25839: nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) <> G_YES)
25840: union all
25841: select /*+ ORDERED USE_NL(QPLATPR QPPROD QPLATPC QPL ) index(QPPROD QP_PRICING_ATTRIBUTES_N5) index(qplatpr qp_preq_line_attrs_tmp_N1) index(qplatpc qp_preq_line_attrs_tmp_N2) l_prod_qual_pric_cur_mod */
25842: qpl.list_header_id,
25843: qpl.list_line_id,
25844: qpl.list_line_type_code,
25845: qplatpr.line_index,
26131: */
26132: CURSOR l_PROD_CUR (p_pricing_phase_id NUMBER) IS
26133: -- Only Product attributes
26134: select /*+ NO_EXPAND ORDERED USE_NL(QPLATPR QPPROD QPH QPL QPRS) INDEX(QPLINES) INDEX(QPLATPR) index(QPPROD QP_PRICING_ATTRIBUTES_N5 ) index(qph QP_LIST_HEADERS_B_N7) */ --5658579
26135: /* ORDERED USE_NL(QPLATPR QPPROD QPH QPL QPRS) index(qplatpr QP_PREQ_LINE_ATTRS_TMP_N1) index(QPPROD QP_PRICING_ATTRIBUTES_N5) index(qph qp_list_headers_b_n7) l_prod_cur_mod */
26136: qpl.list_header_id,
26137: qpl.list_line_id,
26138: qpl.list_line_type_code,
26139: qplatpr.line_index,
26215: OR
26216: (qpl.modifier_level_code = G_LINE_GROUP and
26217: nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) <> G_YES)) -- price book
26218: and qph.active_flag = G_YES
26219: AND ((qph.ASK_FOR_FLAG = G_YES and exists (SELECT /*+ index(la QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/pricebook]
26220: FROM qp_npreq_line_attrs_tmp la
26221: WHERE attribute_type = 'QUALIFIER'
26222: AND pricing_status_code = G_STATUS_UNCHANGED
26223: AND validated_flag = G_NO
26295: */
26296:
26297: CURSOR l_PROD_PRIC_CUR (p_pricing_phase_id NUMBER) IS
26298: --PRODUCT and PRICING attributes
26299: SELECT /*+ ORDERED USE_NL(QPLATPR QPPROD QPLATPC QPH QPL QPRS) index(qplatpr QP_PREQ_LINE_ATTRS_TMP_N1) index(QPPROD QP_PRICING_ATTRIBUTES_N5) index(qplatpc qp_preq_line_attrs_tmp_N2) index(qph qp_list_headers_b_n7) l_prod_pric_cur_mod */
26300: qpl.list_header_id,
26301: qpl.list_line_id,
26302: qpl.list_line_type_code,
26303: qplatpr.line_index,
26384: OR
26385: (qpl.modifier_level_code = G_LINE_GROUP and
26386: nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) <> G_YES)) -- price book
26387: and qph.active_flag = G_YES
26388: AND ((qph.ASK_FOR_FLAG = G_YES and exists (SELECT /*+ index(la QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/pricebook]
26389: FROM qp_npreq_line_attrs_tmp la
26390: WHERE attribute_type = 'QUALIFIER'
26391: AND pricing_status_code = G_STATUS_UNCHANGED
26392: AND validated_flag = G_NO
26508: --AND ((nvl(G_SECURITY_CONTROL, 'OFF') = 'ON' AND G_CURRENT_USER_OP_UNIT = QPH.orig_org_id) OR -- begin security
26509: -- (nvl(G_SECURITY_CONTROL, 'OFF') = 'ON' AND nvl(QPH.global_flag,'Y') = 'Y') OR
26510: -- nvl(G_SECURITY_CONTROL, 'OFF') = 'OFF' ) -- end security
26511: and qph.active_flag=G_YES
26512: AND ((qph.ASK_FOR_FLAG = G_YES and exists (SELECT /*+ index(la QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/pricebook]
26513: FROM qp_npreq_line_attrs_tmp la
26514: WHERE attribute_type = 'QUALIFIER'
26515: AND pricing_status_code = G_STATUS_UNCHANGED
26516: AND validated_flag = G_NO
26585:
26586:
26587: -- Pricing Phase Change
26588: CURSOR l_get_excluded_prods_cur(p_pricing_phase_id NUMBER) IS
26589: SELECT /+ ORDERED USE_NL(b a) INDEX(c qp_preq_line_attrs_tmp_N2) index(b qp_pricing_attributes_n5) l_get_excluded_prods_cur /
26590: b.list_header_id,
26591: b.list_line_id,
26592: c.line_index,
26593: c.validated_flag,
26659:
26660: --hw
26661:
26662: CURSOR l_get_line_index_for_quals IS
26663: SELECT /*+ ORDERED USE_NL(c a) index(a qp_preq_line_attrs_tmp_n2) l_get_line_index_for_quals */distinct
26664: c.LIST_HEADER_ID,
26665: c.LIST_LINE_ID,
26666: c.QUALIFIER_ATTRIBUTE_CONTEXT,
26667: c.QUALIFIER_ATTRIBUTE,
26712: AND a.PRICING_STATUS_CODE = G_STATUS_UNCHANGED
26713: AND c.PRICING_STATUS_CODE = G_STATUS_UNCHANGED
26714: AND c.PRICING_PHASE_ID = p_pricing_phase_id
26715: AND a.LINE_INDEX = b.LINE_INDEX
26716: AND ((c.ASK_FOR_FLAG = G_YES and exists (SELECT /*+ index(la QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/pricebook]
26717: FROM qp_npreq_line_attrs_tmp la
26718: WHERE attribute_type = 'QUALIFIER'
26719: AND pricing_status_code = G_STATUS_UNCHANGED
26720: AND validated_flag = G_NO
29264: UPDATE qp_npreq_ldets_tmp a
29265: SET pricing_status_code = 'D_EXCLUDER'
29266: WHERE pricing_phase_id = p_pricing_phase_id
29267: AND pricing_status_code = G_STATUS_NEW
29268: AND EXISTS ( SELECT/*+ ORDERED USE_NL(c) index(c qp_preq_line_attrs_tmp_N2) index(b QP_PRICING_ATTRIBUTES_N2) l_excl_cur */
29269: 'x'
29270: FROM qp_pricing_attributes b,
29271: qp_npreq_line_attrs_tmp c
29272: WHERE b.list_line_id = a.created_from_list_line_id
31100: -- bug 9210308 added index hint for index QP_LIST_LINES_PK
31101: CURSOR l_prod_qual_cur (p_pricing_phase_id NUMBER) IS
31102: -- Product and Qualifier Only. No pricing
31103: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
31104: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
31105: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
31106: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
31107: INDEX(QPQ QP_QUALIFIERS_N4)
31108: INDEX(QPL QP_LIST_LINES_PK)
31101: CURSOR l_prod_qual_cur (p_pricing_phase_id NUMBER) IS
31102: -- Product and Qualifier Only. No pricing
31103: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
31104: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
31105: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
31106: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
31107: INDEX(QPQ QP_QUALIFIERS_N4)
31108: INDEX(QPL QP_LIST_LINES_PK)
31109: l_prod_qual_cur_pl */
31210: AND ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') ='N' )
31211: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
31212: UNION ALL
31213: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
31214: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
31215: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
31216: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
31217: INDEX(QPQ QP_QUALIFIERS_N5)
31218: INDEX(QPL QP_LIST_LINES_PK)
31211: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
31212: UNION ALL
31213: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
31214: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
31215: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
31216: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
31217: INDEX(QPQ QP_QUALIFIERS_N5)
31218: INDEX(QPL QP_LIST_LINES_PK)
31219: l_prod_qual_cur_pl */
31320: AND ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') ='N' )
31321: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
31322: UNION ALL
31323: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
31324: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
31325: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
31326: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
31327: INDEX(QPQ QP_QUALIFIERS_N6)
31328: INDEX(QPL QP_LIST_LINES_PK)
31321: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
31322: UNION ALL
31323: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
31324: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
31325: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
31326: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
31327: INDEX(QPQ QP_QUALIFIERS_N6)
31328: INDEX(QPL QP_LIST_LINES_PK)
31329: l_prod_qual_cur_pl */
31443: -- 3. Split into unions ('=', 'BETWEEN', 'NOT =') for better use of indexes
31444: CURSOR l_prod_qual_cur_currency (p_pricing_phase_id NUMBER) IS
31445: -- Product and Qualifier Only. No pricing
31446: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
31447: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
31448: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
31449: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
31450: INDEX(QPQ QP_QUALIFIERS_N4)
31451: INDEX(QPL QP_LIST_LINES_PK)
31444: CURSOR l_prod_qual_cur_currency (p_pricing_phase_id NUMBER) IS
31445: -- Product and Qualifier Only. No pricing
31446: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
31447: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
31448: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
31449: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
31450: INDEX(QPQ QP_QUALIFIERS_N4)
31451: INDEX(QPL QP_LIST_LINES_PK)
31452: l_prod_qual_cur_pl */
31531: nvl(trunc(qcdt.end_date_active),TRUNC(qplines.pricing_effective_date))
31532: AND (
31533: (qcdt.curr_attribute_context is null
31534: and not exists
31535: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
31536: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1
31537: where qcdt1.curr_attribute_context IS NOT NULL
31538: and qcdt1.curr_attribute_context = pa_tmp.context
31539: and qcdt1.curr_attribute = pa_tmp.attribute
31548: and nvl(trunc(qcdt1.end_date_active),TRUNC(qplines.pricing_effective_date))))
31549: OR
31550: (qcdt.curr_attribute_context is not null
31551: and qcdt.precedence =
31552: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
31553: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2
31554: where qcdt2.curr_attribute_context IS NOT NULL
31555: and qcdt2.curr_attribute_context = pa_tmp1.context
31556: and qcdt2.curr_attribute = pa_tmp1.attribute
31600: AND ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') ='N' )
31601: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
31602: UNION ALL
31603: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
31604: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
31605: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
31606: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
31607: INDEX(QPQ QP_QUALIFIERS_N5)
31608: INDEX(QPL QP_LIST_LINES_PK)
31601: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
31602: UNION ALL
31603: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
31604: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
31605: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
31606: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
31607: INDEX(QPQ QP_QUALIFIERS_N5)
31608: INDEX(QPL QP_LIST_LINES_PK)
31609: l_prod_qual_cur_pl */
31688: nvl(trunc(qcdt.end_date_active),TRUNC(qplines.pricing_effective_date))
31689: AND (
31690: (qcdt.curr_attribute_context is null
31691: and not exists
31692: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
31693: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1
31694: where qcdt1.curr_attribute_context IS NOT NULL
31695: and qcdt1.curr_attribute_context = pa_tmp.context
31696: and qcdt1.curr_attribute = pa_tmp.attribute
31705: and nvl(trunc(qcdt1.end_date_active),TRUNC(qplines.pricing_effective_date))))
31706: OR
31707: (qcdt.curr_attribute_context is not null
31708: and qcdt.precedence =
31709: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
31710: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2
31711: where qcdt2.curr_attribute_context IS NOT NULL
31712: and qcdt2.curr_attribute_context = pa_tmp1.context
31713: and qcdt2.curr_attribute = pa_tmp1.attribute
31757: AND ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') ='N' )
31758: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
31759: UNION ALL
31760: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
31761: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
31762: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
31763: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
31764: INDEX(QPQ QP_QUALIFIERS_N6)
31765: INDEX(QPL QP_LIST_LINES_PK)
31758: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
31759: UNION ALL
31760: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
31761: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
31762: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
31763: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
31764: INDEX(QPQ QP_QUALIFIERS_N6)
31765: INDEX(QPL QP_LIST_LINES_PK)
31766: l_prod_qual_cur_pl */
31845: nvl(trunc(qcdt.end_date_active),TRUNC(qplines.pricing_effective_date))
31846: AND (
31847: (qcdt.curr_attribute_context is null
31848: and not exists
31849: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
31850: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1
31851: where qcdt1.curr_attribute_context IS NOT NULL
31852: and qcdt1.curr_attribute_context = pa_tmp.context
31853: and qcdt1.curr_attribute = pa_tmp.attribute
31862: and nvl(trunc(qcdt1.end_date_active),TRUNC(qplines.pricing_effective_date))))
31863: OR
31864: (qcdt.curr_attribute_context is not null
31865: and qcdt.precedence =
31866: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
31867: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2
31868: where qcdt2.curr_attribute_context IS NOT NULL
31869: and qcdt2.curr_attribute_context = pa_tmp1.context
31870: and qcdt2.curr_attribute = pa_tmp1.attribute
31920: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
31921: ORDER BY 4,2;
31922:
31923: --Added INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5) for 3259932
31924: --[julin/4708044] added index(qplapr QP_PREQ_LINE_ATTRS_TMP_N1), use_nl(qprs)
31925: CURSOR l_prod_qual_pric_cur(p_pricing_phase_id NUMBER) IS
31926: --Product+Qualifiers+Pricing
31927: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
31928: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
31924: --[julin/4708044] added index(qplapr QP_PREQ_LINE_ATTRS_TMP_N1), use_nl(qprs)
31925: CURSOR l_prod_qual_pric_cur(p_pricing_phase_id NUMBER) IS
31926: --Product+Qualifiers+Pricing
31927: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
31928: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
31929: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
31930: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
31931: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
31932: INDEX(QPQ QP_QUALIFIERS_N4)
31925: CURSOR l_prod_qual_pric_cur(p_pricing_phase_id NUMBER) IS
31926: --Product+Qualifiers+Pricing
31927: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
31928: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
31929: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
31930: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
31931: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
31932: INDEX(QPQ QP_QUALIFIERS_N4)
31933: l_prod_qual_pric_cur_pl */
31926: --Product+Qualifiers+Pricing
31927: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
31928: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
31929: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
31930: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
31931: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
31932: INDEX(QPQ QP_QUALIFIERS_N4)
31933: l_prod_qual_pric_cur_pl */
31934: qpl.list_header_id,
32052: AND ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') ='N' )
32053: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
32054: UNION ALL
32055: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
32056: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
32057: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
32058: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
32059: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
32060: INDEX(QPQ QP_QUALIFIERS_N5)
32053: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
32054: UNION ALL
32055: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
32056: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
32057: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
32058: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
32059: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
32060: INDEX(QPQ QP_QUALIFIERS_N5)
32061: l_prod_qual_pric_cur_pl */
32054: UNION ALL
32055: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
32056: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
32057: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
32058: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
32059: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
32060: INDEX(QPQ QP_QUALIFIERS_N5)
32061: l_prod_qual_pric_cur_pl */
32062: qpl.list_header_id,
32180: AND ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') ='N' )
32181: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
32182: UNION ALL
32183: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
32184: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
32185: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
32186: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
32187: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
32188: INDEX(QPQ QP_QUALIFIERS_N6)
32181: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
32182: UNION ALL
32183: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
32184: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
32185: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
32186: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
32187: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
32188: INDEX(QPQ QP_QUALIFIERS_N6)
32189: l_prod_qual_pric_cur_pl */
32182: UNION ALL
32183: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
32184: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
32185: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
32186: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
32187: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
32188: INDEX(QPQ QP_QUALIFIERS_N6)
32189: l_prod_qual_pric_cur_pl */
32190: qpl.list_header_id,
32314: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
32315: ORDER BY 4,2;
32316:
32317: --Added INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5) for 3259932
32318: --[julin/4708044] added index(qplapr QP_PREQ_LINE_ATTRS_TMP_N1), use_nl(qprs)
32319: --[julin/5007789]
32320: -- 1. Performing line filter first.
32321: -- 2. Performing qualifier filter before product filter
32322: -- 3. Split into unions ('=', 'BETWEEN', 'NOT =') for better use of indexes
32322: -- 3. Split into unions ('=', 'BETWEEN', 'NOT =') for better use of indexes
32323: CURSOR l_prod_qual_pric_cur_currency(p_pricing_phase_id NUMBER) IS
32324: --Product+Qualifiers+Pricing
32325: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
32326: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
32327: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
32328: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
32329: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
32330: INDEX(QPQ QP_QUALIFIERS_N4)
32323: CURSOR l_prod_qual_pric_cur_currency(p_pricing_phase_id NUMBER) IS
32324: --Product+Qualifiers+Pricing
32325: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
32326: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
32327: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
32328: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
32329: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
32330: INDEX(QPQ QP_QUALIFIERS_N4)
32331: l_prod_qual_pric_cur_pl */
32324: --Product+Qualifiers+Pricing
32325: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
32326: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
32327: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
32328: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
32329: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
32330: INDEX(QPQ QP_QUALIFIERS_N4)
32331: l_prod_qual_pric_cur_pl */
32332: qpl.list_header_id,
32419: and nvl(trunc(qcdt.end_date_active),TRUNC(qplines.pricing_effective_date))
32420: AND (
32421: (qcdt.curr_attribute_context is null
32422: and not exists
32423: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
32424: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1
32425: where qcdt1.curr_attribute_context IS NOT NULL
32426: and qcdt1.curr_attribute_context = pa_tmp.context
32427: and qcdt1.curr_attribute = pa_tmp.attribute
32436: and nvl(trunc(qcdt1.end_date_active),TRUNC(qplines.pricing_effective_date))))
32437: OR
32438: (qcdt.curr_attribute_context is not null
32439: and qcdt.precedence =
32440: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
32441: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2
32442: where qcdt2.curr_attribute_context IS NOT NULL
32443: and qcdt2.curr_attribute_context = pa_tmp1.context
32444: and qcdt2.curr_attribute = pa_tmp1.attribute
32497: AND ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') ='N' )
32498: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
32499: UNION ALL
32500: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
32501: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
32502: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
32503: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
32504: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
32505: INDEX(QPQ QP_QUALIFIERS_N5)
32498: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
32499: UNION ALL
32500: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
32501: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
32502: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
32503: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
32504: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
32505: INDEX(QPQ QP_QUALIFIERS_N5)
32506: l_prod_qual_pric_cur_pl */
32499: UNION ALL
32500: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
32501: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
32502: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
32503: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
32504: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
32505: INDEX(QPQ QP_QUALIFIERS_N5)
32506: l_prod_qual_pric_cur_pl */
32507: qpl.list_header_id,
32594: and nvl(trunc(qcdt.end_date_active),TRUNC(qplines.pricing_effective_date))
32595: AND (
32596: (qcdt.curr_attribute_context is null
32597: and not exists
32598: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
32599: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1
32600: where qcdt1.curr_attribute_context IS NOT NULL
32601: and qcdt1.curr_attribute_context = pa_tmp.context
32602: and qcdt1.curr_attribute = pa_tmp.attribute
32611: and nvl(trunc(qcdt1.end_date_active),TRUNC(qplines.pricing_effective_date))))
32612: OR
32613: (qcdt.curr_attribute_context is not null
32614: and qcdt.precedence =
32615: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
32616: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2
32617: where qcdt2.curr_attribute_context IS NOT NULL
32618: and qcdt2.curr_attribute_context = pa_tmp1.context
32619: and qcdt2.curr_attribute = pa_tmp1.attribute
32672: AND ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') ='N' )
32673: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
32674: UNION ALL
32675: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
32676: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
32677: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
32678: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
32679: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
32680: INDEX(QPQ QP_QUALIFIERS_N6)
32673: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
32674: UNION ALL
32675: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
32676: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
32677: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
32678: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
32679: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
32680: INDEX(QPQ QP_QUALIFIERS_N6)
32681: l_prod_qual_pric_cur_pl */
32674: UNION ALL
32675: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
32676: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
32677: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
32678: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
32679: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
32680: INDEX(QPQ QP_QUALIFIERS_N6)
32681: l_prod_qual_pric_cur_pl */
32682: qpl.list_header_id,
32769: and nvl(trunc(qcdt.end_date_active),TRUNC(qplines.pricing_effective_date))
32770: AND (
32771: (qcdt.curr_attribute_context is null
32772: and not exists
32773: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
32774: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1
32775: where qcdt1.curr_attribute_context IS NOT NULL
32776: and qcdt1.curr_attribute_context = pa_tmp.context
32777: and qcdt1.curr_attribute = pa_tmp.attribute
32786: and nvl(trunc(qcdt1.end_date_active),TRUNC(qplines.pricing_effective_date))))
32787: OR
32788: (qcdt.curr_attribute_context is not null
32789: and qcdt.precedence =
32790: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
32791: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2
32792: where qcdt2.curr_attribute_context IS NOT NULL
32793: and qcdt2.curr_attribute_context = pa_tmp1.context
32794: and qcdt2.curr_attribute = pa_tmp1.attribute
32858: -- 2. Merged qplatpr subquery conditions into main where clause.
32859: CURSOR l_prod_cur (p_pricing_phase_id NUMBER) IS
32860: -- Only Product attributes
32861: -- bug 9210308 - Added index hint for index QP_LIST_LINES_PK
32862: SELECT /*+ ORDERED USE_NL(QPLATPR QPPROD QPL QPH QPRS) index(qplatpr QP_PREQ_LINE_ATTRS_TMP_N1) index(QPL QP_LIST_LINES_PK) l_prod_cur_pl */
32863: qpl.list_header_id,
32864: qpl.list_line_id,
32865: qpl.list_line_type_code,
32866: qplatpr.line_index,
32939: --[julin/5007789] Tuned query as follows:
32940: -- 1. Performing line filter first.
32941: CURSOR l_prod_cur_currency (p_pricing_phase_id NUMBER) IS
32942: -- Product Only.
32943: SELECT /*+ ORDERED USE_NL(QPLATPR QPPROD QPL QPH QPRS) index(qplatpr QP_PREQ_LINE_ATTRS_TMP_N1) INDEX(QPL QP_LIST_LINES_PK) l_prod_cur_pl */
32944: qpl.list_header_id,
32945: qpl.list_line_id,
32946: qpl.list_line_type_code,
32947: qplatpr.line_index,
33008: and nvl(trunc(qcdt.end_date_active),TRUNC(qplines.pricing_effective_date))
33009: AND (
33010: (qcdt.curr_attribute_context is null
33011: and not exists
33012: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
33013: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1
33014: where qcdt1.curr_attribute_context IS NOT NULL
33015: and qcdt1.curr_attribute_context = pa_tmp.context
33016: and qcdt1.curr_attribute = pa_tmp.attribute
33025: and nvl(trunc(qcdt1.end_date_active),TRUNC(qplines.pricing_effective_date))))
33026: OR
33027: (qcdt.curr_attribute_context is not null
33028: and qcdt.precedence =
33029: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
33030: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2
33031: where qcdt2.curr_attribute_context IS NOT NULL
33032: and qcdt2.curr_attribute_context = pa_tmp1.context
33033: and qcdt2.curr_attribute = pa_tmp1.attribute
33070: -- 2. Merged qplatpr subquery conditions into main where clause.
33071: -- 3. Merged qplatpc subquery conditions into main where clause.
33072: CURSOR l_prod_pric_cur (p_pricing_phase_id NUMBER) IS
33073: --PRODUCT and PRICING attributes
33074: SELECT /*+ ORDERED USE_NL(QPLATPR QPPROD QPLATPC QPL QPH QPRS) index(qplatpr QP_PREQ_LINE_ATTRS_TMP_N1) INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5) index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2) l_prod_pric_cur_pl */
33075: qpl.list_header_id,
33076: qpl.list_line_id,
33077: qpl.list_line_type_code,
33078: qplatpr.line_index,
33170: --[julin/5007789] Tuned query as follows:
33171: -- 1. Performing line filter first.
33172: CURSOR l_prod_pric_cur_currency(p_pricing_phase_id NUMBER) IS
33173: --Product+Pricing
33174: SELECT /*+ ORDERED USE_NL(QPLATPR QPPROD QPLATPC QPL QPH QPRS) index(qplatpr QP_PREQ_LINE_ATTRS_TMP_N1) INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5) index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2) l_prod_pric_cur_pl */
33175: qpl.list_header_id,
33176: qpl.list_line_id,
33177: qpl.list_line_type_code,
33178: qplatpr.line_index,
33248: nvl(trunc(qcdt.end_date_active),TRUNC(qplines.pricing_effective_date))
33249: AND (
33250: (qcdt.curr_attribute_context is null
33251: and not exists
33252: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
33253: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1
33254: where qcdt1.curr_attribute_context IS NOT NULL
33255: and qcdt1.curr_attribute_context = pa_tmp.context
33256: and qcdt1.curr_attribute = pa_tmp.attribute
33265: and nvl(trunc(qcdt1.end_date_active),TRUNC(qplines.pricing_effective_date))))
33266: OR
33267: (qcdt.curr_attribute_context is not null
33268: and qcdt.precedence =
33269: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
33270: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2
33271: where qcdt2.curr_attribute_context IS NOT NULL
33272: and qcdt2.curr_attribute_context = pa_tmp1.context
33273: and qcdt2.curr_attribute = pa_tmp1.attribute
35043: INDX,lQP_PREQ_GRP.create_qualifier_from_list._check_modlist_cur,qp_npreq_line_attrs_tmp_N2,VALUE_FROM,6
35044: */
35045: --check if passed in price list indeed has qualified
35046: CURSOR l_check_modlist_cur(p_line_index IN PLS_INTEGER) IS
35047: SELECT /*+ ORDERED index(qplat qp_preq_line_attrs_tmp_n2) l_check_modlist_cur */ 'X'
35048: FROM qp_npreq_ldets_tmp qpld,
35049: qp_npreq_line_attrs_tmp qplat
35050: WHERE qpld.line_index = p_line_index
35051: AND qpld.pricing_phase_id = G_PRICE_LIST_PHASE_ID
35062: SELECT /*+ index(qpll QP_PREQ_LINES_TMP_N2) */ qpll.line_index
35063: FROM qp_npreq_lines_tmp qpll
35064: WHERE qpll.line_type_code <> G_ORDER_LEVEL
35065: AND NOT EXISTS (
35066: SELECT /*+ ORDERED index(qpld QP_PREQ_LDETS_TMP_N1) index(qplat qp_preq_line_attrs_tmp_n2) l_check_modlist_cur */ 'X'
35067: FROM qp_npreq_ldets_tmp qpld,
35068: qp_npreq_line_attrs_tmp qplat
35069: WHERE qpld.line_index = qpll.line_index
35070: AND qpld.pricing_phase_id = G_PRICE_LIST_PHASE_ID
37417: ,MODIFIER_LEVEL_CODE
37418: ,PRIMARY_UOM_FLAG
37419: ,REQUEST_ID
37420: ,DERIVED_QUALIFIER_FLAG)
37421: SELECT /*+ ORDERED use_nl(qpc qs hn) index(qnlat QP_PREQ_LINE_ATTRS_TMP_N2) */
37422: qnlat.LINE_INDEX
37423: ,qnlat.LINE_DETAIL_INDEX
37424: ,qnlat.ATTRIBUTE_LEVEL
37425: ,qnlat.ATTRIBUTE_TYPE
37502: WHILE lower_limit <= G_MAX_LINE_INDEX
37503: LOOP
37504: upper_limit := lower_limit + G_CHUNK_SIZE;
37505: UPDATE
37506: qp_npreq_line_attrs_tmp /*+ index(attr qp_preq_line_attrs_tmp_n9) */attr
37507: SET attr.segment_id = (
37508: SELECT qps.segment_id
37509: FROM qp_prc_contexts_b qpc
37510: , qp_segments_b qps
37624: WHILE lower_limit <= G_MAX_LINE_INDEX
37625: LOOP
37626: upper_limit := lower_limit + G_CHUNK_SIZE;
37627:
37628: --change in index qp_preq_line_attrs_tmp_n9???
37629:
37630: INSERT INTO qp_npreq_line_patrns_tmp(line_index, pattern_id, hash_key)
37631: SELECT /*+ ORDERED index(attr qp_preq_line_attrs_tmp_n9) */
37632: attr.line_index,
37627:
37628: --change in index qp_preq_line_attrs_tmp_n9???
37629:
37630: INSERT INTO qp_npreq_line_patrns_tmp(line_index, pattern_id, hash_key)
37631: SELECT /*+ ORDERED index(attr qp_preq_line_attrs_tmp_n9) */
37632: attr.line_index,
37633: qpp.pattern_id,
37634: sys_connect_by_path(attr.value_from, '|') hash_key
37635: FROM (SELECT /*+ index(qpp1 qp_pattern_phases_n1) */ *
37649: lower_limit := upper_limit + 1;
37650: END LOOP;
37651: /* ELSE
37652: INSERT INTO qp_npreq_line_patrns_tmp(line_index, pattern_id, hash_key)
37653: SELECT /*+ ORDERED index(attr qp_preq_line_attrs_tmp_n9) */
37654: /* attr.line_index,
37655: qpp.pattern_id,
37656: sys_connect_by_path(attr.value_from, '|') hash_key
37657: FROM (SELECT /*+ index(qpp1 qp_pattern_phases_n1) */ --*
37811: AND TRUNC(nvl(atrgrps.end_date_active_q, nvl(qplines.pricing_effective_date, sysdate)))
37812: AND((atrgrps.ask_for_flag = qp_preq_grp.g_yes
37813: AND EXISTS
37814: (SELECT
37815: /*+ index(la QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/pricebook/backport]
37816: FROM qp_npreq_line_attrs_tmp la -- will check whether this can be moved to shaneeds code
37817: WHERE attribute_type = G_QUALIFIER_TYPE
37818: AND pricing_status_code = qp_preq_grp.g_status_unchanged
37819: AND validated_flag = qp_preq_grp.g_no
37883: AND((atrgrps.ask_for_flag = qp_preq_grp.g_yes
37884: AND EXISTS
37885: (SELECT
37886:
37887: /*+ index(la QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/pricebook/backport]
37888: FROM qp_npreq_line_attrs_tmp la -- will check whether this can be moved to shaneeds code
37889: WHERE attribute_type = G_QUALIFIER_TYPE
37890: AND pricing_status_code = qp_preq_grp.g_status_unchanged
37891: AND validated_flag = qp_preq_grp.g_no
37971: AND((atrgrps.ask_for_flag = qp_preq_grp.g_yes
37972: AND EXISTS
37973: (SELECT
37974:
37975: /*+ index(la QP_PREQ_LINE_ATTRS_TMP_N1) */ /*'x' --[julin/pricebook/backport]
37976: FROM qp_npreq_line_attrs_tmp la -- will check whether this can be moved to shaneeds code
37977: WHERE attribute_type = G_QUALIFIER_TYPE
37978: AND pricing_status_code = qp_preq_grp.g_status_unchanged
37979: AND validated_flag = qp_preq_grp.g_no
38070: AND((atrgrps.ask_for_flag = qp_preq_grp.g_yes
38071: AND EXISTS
38072: (SELECT
38073:
38074: /*+ index(la QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/pricebook/backport]
38075: FROM qp_npreq_line_attrs_tmp la -- will check whether this can be moved to shaneeds code
38076: WHERE attribute_type = G_QUALIFIER_TYPE
38077: AND pricing_status_code = qp_preq_grp.g_status_unchanged
38078: AND validated_flag = qp_preq_grp.g_no
38230: AND((atrgrps_h.ask_for_flag = qp_preq_grp.g_yes
38231: AND EXISTS
38232: (SELECT
38233:
38234: /*+ index(la QP_PREQ_LINE_ATTRS_TMP_N1) */ /*'x' --[julin/pricebook/backport]
38235: FROM qp_npreq_line_attrs_tmp la -- will check whether this can be moved to shaneeds code
38236: WHERE attribute_type = G_QUALIFIER_TYPE
38237: AND pricing_status_code = qp_preq_grp.g_status_unchanged
38238: AND validated_flag = qp_preq_grp.g_no
39165: WHERE pricing_phase_id = p_pricing_phase_id
39166: AND pricing_status_code = qp_preq_grp.g_status_unchanged
39167: AND EXISTS
39168: (SELECT
39169: /*+ ORDERED USE_NL(c) index(c qp_preq_line_attrs_tmp_N2) index(b QP_PRICING_ATTRIBUTES_N2) l_excl_cur */ 'x'
39170: FROM qp_pricing_attributes b,
39171: qp_npreq_line_attrs_tmp c
39172: WHERE b.list_line_id = a.list_line_id
39173: AND b.excluder_flag = qp_preq_grp.g_yes
39292: AND stage = p_stage
39293: AND pricing_phase_id = p_pricing_phase_id
39294: AND null_other_oprt_count <> 0
39295: AND NOT EXISTS(
39296: SELECT /*+ index(b qp_preq_line_attrs_tmp_n10) */ 1
39297: FROM qp_npreq_line_attrs_tmp b
39298: WHERE b.line_index = a.line_index
39299: AND b.list_header_id = a.list_header_id
39300: AND b.list_line_id = a.list_line_id
39317: AND pricing_phase_id = p_pricing_phase_id
39318: AND grouping_no <> -1
39319: AND other_oprt_count > 0
39320: AND NOT EXISTS(
39321: SELECT /*+ index(b qp_preq_line_attrs_tmp_n10) */ 1
39322: FROM qp_npreq_line_attrs_tmp b
39323: WHERE b.line_index = a.line_index
39324: AND b.list_header_id = a.list_header_id
39325: AND b.list_line_id = a.list_line_id
39665: --SEGMENT_ID,
39666: derived_qualifier_flag
39667: )
39668: -- Product Attributes
39669: SELECT /*+ ORDERED index(qplaptr qp_preq_line_attrs_tmp_n8) */
39670: qpldets.LINE_INDEX, --LINE_INDEX
39671: qpldets.LINE_DETAIL_INDEX, --LINE_DETAIL_INDEX
39672: qplaptr.ATTRIBUTE_LEVEL, --ATTRIBUTE_LEVEL
39673: qplaptr.ATTRIBUTE_TYPE, --ATTRIBUTE_TYPE
39714: AND qplaptr.segment_id = qppatr.PRODUCT_SEGMENT_ID
39715: AND qplaptr.attribute_type=G_PRODUCT_TYPE
39716: UNION
39717: -- Pricing Attributes
39718: SELECT /*+ ORDERED index(qplaptr qp_preq_line_attrs_tmp_n8) */
39719: qpldets.LINE_INDEX, --LINE_INDEX
39720: qpldets.LINE_DETAIL_INDEX, --LINE_DETAIL_INDEX
39721: qplaptr.ATTRIBUTE_LEVEL, --ATTRIBUTE_LEVEL
39722: qplaptr.ATTRIBUTE_TYPE, --ATTRIBUTE_TYPE
39849: PRIMARY_UOM_FLAG,
39850: --SEGMENT_ID,
39851: derived_qualifier_flag
39852: )
39853: SELECT /*+ ORDERED index(qpq QP_QUALIFIERS_N1) index(qplines qp_preq_line_attrs_tmp_n8) index(qppatq QP_PREQ_PATRN_QUAL_T_N2) */
39854: qpldets.LINE_INDEX, --LINE_INDEX,
39855: qpldets.LINE_DETAIL_INDEX, -- LINE_DETAIL_INDEX,
39856: qplines.ATTRIBUTE_LEVEL, -- ATTRIBUTE_LEVEL,
39857: qplines.ATTRIBUTE_TYPE, -- ATTRIBUTE_TYPE,
39901: AND qplines.ATTRIBUTE_TYPE = G_QUALIFIER_TYPE
39902: AND qplines.LINE_INDEX = qppatq.LINE_INDEX
39903: UNION
39904: -- -1 qualifiers if other group exist
39905: SELECT /*+ ORDERED index(qpq QP_QUALIFIERS_N1) index(qplines qp_preq_line_attrs_tmp_n8) index(qppatq QP_PREQ_PATRN_QUAL_T_N2) */
39906: qpldets.LINE_INDEX, --LINE_INDEX,
39907: qpldets.LINE_DETAIL_INDEX, -- LINE_DETAIL_INDEX,
39908: qplines.ATTRIBUTE_LEVEL, -- ATTRIBUTE_LEVEL,
39909: qplines.ATTRIBUTE_TYPE, -- ATTRIBUTE_TYPE,
40013: PRIMARY_UOM_FLAG,
40014: --SEGMENT_ID,
40015: derived_qualifier_flag
40016: )
40017: SELECT /*+ ORDERED index(qpq QP_QUALIFIERS_N1) index(qplines qp_preq_line_attrs_tmp_n8) index(qppatq QP_PREQ_PATRN_QUAL_T_N2) */
40018: qpldets.LINE_INDEX, --LINE_INDEX,
40019: qpldets.LINE_DETAIL_INDEX, -- LINE_DETAIL_INDEX,
40020: qplines.ATTRIBUTE_LEVEL, -- ATTRIBUTE_LEVEL,
40021: qplines.ATTRIBUTE_TYPE, -- ATTRIBUTE_TYPE,
40064: AND qplines.SEGMENT_ID = qpq.SEGMENT_ID
40065: AND qplines.LINE_INDEX = qppatq.LINE_INDEX
40066: UNION all
40067: -- -1 qualifiers if other group exist
40068: SELECT /*+ ORDERED index(qpq QP_QUALIFIERS_N1) index(qplines qp_preq_line_attrs_tmp_n8) index(qppatq QP_PREQ_PATRN_QUAL_T_N2) */
40069: qpldets.LINE_INDEX, --LINE_INDEX,
40070: qpldets.LINE_DETAIL_INDEX, -- LINE_DETAIL_INDEX,
40071: qplines.ATTRIBUTE_LEVEL, -- ATTRIBUTE_LEVEL,
40072: qplines.ATTRIBUTE_TYPE, -- ATTRIBUTE_TYPE,
40240: MODIFIER_LEVEL_CODE,
40241: --SEGMENT_ID,
40242: derived_qualifier_flag
40243: )
40244: SELECT /*+ ORDERED index(qpq QP_QUALIFIERS_N1) index(qplines qp_preq_line_attrs_tmp_n8) index(qppatq QP_PREQ_PATRN_QUAL_T_N2) */
40245: qppatq.LINE_INDEX, --LINE_INDEX,
40246: NULL, -- LINE_DETAIL_INDEX,
40247: qplines.ATTRIBUTE_LEVEL, -- ATTRIBUTE_LEVEL,
40248: QP_PREQ_GRP.G_QUALIFIER_TYPE, -- ATTRIBUTE_TYPE,
40288: AND ((nvl(qplines.DERIVED_QUALIFIER_FLAG,'N')='N')
40289: OR (qplines.DERIVED_QUALIFIER_FLAG = 'Y' AND QPQ.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
40290: UNION all
40291: -- -1 qualifiers if other group exist
40292: SELECT /*+ ORDERED index(qpq QP_QUALIFIERS_N1) index(qplines qp_preq_line_attrs_tmp_n8) index(qppatq QP_PREQ_PATRN_QUAL_T_N2) */
40293: qppatq.LINE_INDEX, --LINE_INDEX,
40294: null, -- LINE_DETAIL_INDEX,
40295: qplines.ATTRIBUTE_LEVEL, -- ATTRIBUTE_LEVEL,
40296: QP_PREQ_GRP.G_QUALIFIER_TYPE, -- ATTRIBUTE_TYPE,
40390: PRIMARY_UOM_FLAG,
40391: --SEGMENT_ID,
40392: derived_qualifier_flag
40393: )
40394: SELECT /*+ index(qplaptr qp_preq_line_attrs_tmp_n8) */
40395: qppat.LINE_INDEX, --LINE_INDEX
40396: NULL, --LINE_DETAIL_INDEX
40397: qplaptr.ATTRIBUTE_LEVEL, --ATTRIBUTE_LEVEL
40398: QP_PREQ_GRP.G_PRICING_TYPE, --ATTRIBUTE_TYPE
40491: MODIFIER_LEVEL_CODE,
40492: --SEGMENT_ID,
40493: derived_qualifier_flag
40494: )
40495: SELECT /*+ ORDERED index(qpq QP_QUALIFIERS_N1) index(qplines qp_preq_line_attrs_tmp_n8) index(qppatq QP_PREQ_PATRN_QUAL_T_N2) */
40496: qppatq.LINE_INDEX, --LINE_INDEX,
40497: NULL, -- LINE_DETAIL_INDEX,
40498: qplines.ATTRIBUTE_LEVEL, -- ATTRIBUTE_LEVEL,
40499: QP_PREQ_GRP.G_QUALIFIER_TYPE, -- ATTRIBUTE_TYPE,
40540: OR (qplines.DERIVED_QUALIFIER_FLAG = 'Y' AND QPQ.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
40541: UNION all
40542: -- -1 qualifiers if other group exist
40543: SELECT /*+ ORDERED index(qpq QP_QUALIFIERS_N1)
40544: index(qplines qp_preq_line_attrs_tmp_n8)
40545: index(qppatq QP_PREQ_PATRN_QUAL_T_N2) */
40546: DISTINCT qppatq.LINE_INDEX, --LINE_INDEX,
40547: null, -- LINE_DETAIL_INDEX,
40548: qplines.ATTRIBUTE_LEVEL, -- ATTRIBUTE_LEVEL,
42387:
42388: --[julin/pricebook] added hint
42389: UPDATE qp_npreq_lines_tmp a
42390: SET (price_list_header_id,validated_flag) =
42391: (SELECT /*+ index(b QP_PREQ_LINE_ATTRS_TMP_N2) */ distinct qp_number.canonical_to_number(value_from),validated_flag
42392: FROM qp_npreq_line_attrs_tmp b
42393: WHERE a.line_index = b.line_index
42394: AND context = G_LIST_HEADER_CONTEXT
42395: AND attribute = G_PRICELIST_ATTRIBUTE