[Home] [Help]
718: -- Changed the cursor l_outer_qual_cur for bug#3002632
719: --Added index hints for bug#7480862
720: CURSOR l_MINI_OUTER_QUAL_CUR IS
721: -- Qualifier Only
722: select /*+ ORDERED NO_EXPAND USE_NL(qpq qplatq qph) index (qplatq,QP_PREQ_LINE_ATTRS_TMP_N6)
723: index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_outer_qual_cur_mod */
724: qpq.list_header_id,
725: qpq.list_line_id list_line_id,
726: qpq.qualifier_group_cnt,
813: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
814: --and qptq.pricing_status_code = G_STATUS_UNCHANGED
815: --and qptq.pricing_phase_id = p_pricing_phase_id
816: union all
817: 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
818: uter_qual_cur_mod */
819: qpq.list_header_id,
820: qpq.list_line_id list_line_id,
821: qpq.qualifier_group_cnt,
906: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
907: --and qptq.pricing_status_code = G_STATUS_UNCHANGED
908: --and qptq.pricing_phase_id = p_pricing_phase_id
909: union all
910: 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 */
911: qpq.list_header_id,
912: qpq.list_line_id list_line_id,
913: qpq.qualifier_group_cnt,
914: qpq.others_group_cnt,
1002: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
1003: --and qptq.pricing_status_code = G_STATUS_UNCHANGED
1004: --and qptq.pricing_phase_id = p_pricing_phase_id
1005: union all
1006: 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
1007: ter_qual_cur_mod */
1008: qpq.list_header_id,
1009: qpq.list_line_id list_line_id,
1010: qpq.qualifier_group_cnt,
1097: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
1098: --and qptq.pricing_status_code = G_STATUS_UNCHANGED
1099: --and qptq.pricing_phase_id = p_pricing_phase_id
1100: union all
1101: 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 */
1102: qpq.list_header_id,
1103: qpq.list_line_id list_line_id,
1104: qpq.qualifier_group_cnt,
1105: qpq.others_group_cnt,
1194: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
1195: --and qptq.pricing_status_code = G_STATUS_UNCHANGED
1196: --and qptq.pricing_phase_id = p_pricing_phase_id
1197: union all
1198: 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
1199: er_qual_cur_mod */
1200: qpq.list_header_id,
1201: qpq.list_line_id list_line_id,
1202: qpq.qualifier_group_cnt,
1290: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
1291: --and qptq.pricing_status_code = G_STATUS_UNCHANGED
1292: --and qptq.pricing_phase_id = p_pricing_phase_id
1293: union all
1294: select /*+ ORDERED USE_NL(qpq qplh qph) index (qplatq,QP_PREQ_LINE_ATTRS_TMP_N7)
1295: index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_outer_qual_cur_mod */
1296: qpq.list_header_id,
1297: qpq.list_line_id list_line_id,
1298: qpq.qualifier_group_cnt,
1385: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
1386: --and qptq.pricing_status_code = G_STATUS_UNCHANGED
1387: --and qptq.pricing_phase_id = p_pricing_phase_id
1388: union all
1389: select /*+ ORDERED USE_NL(qpq qplh qph) index (qplatq,QP_PREQ_LINE_ATTRS_TMP_N7)
1390: index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_outer_qual_cur_mod */
1391: qpq.list_header_id,
1392: qpq.list_line_id list_line_id,
1393: qpq.qualifier_group_cnt,
1679: -- [julin/4396043/4471795] perform header filter before prod check, go to qp_pricing_attributes
1680: -- before qp_npreq_line_attrs_tmp during prod check.
1681: CURSOR l_OUTER_QUAL_CUR IS
1682: -- Qualifier Only
1683: 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 */
1684: qpq.list_header_id,
1685: qpq.list_line_id list_line_id,
1686: qpq.qualifier_group_cnt,
1687: qpq.others_group_cnt,
1794: and qph.source_system_code = qprs.source_system_code
1795: and ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') = 'N')
1796: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
1797: union all
1798: 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 */
1799: qpq.list_header_id,
1800: qpq.list_line_id list_line_id,
1801: qpq.qualifier_group_cnt,
1802: qpq.others_group_cnt,
1911: and qph.source_system_code = qprs.source_system_code
1912: and ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') = 'N')
1913: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
1914: union all
1915: 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 */
1916: qpq.list_header_id,
1917: qpq.list_line_id list_line_id,
1918: qpq.qualifier_group_cnt,
1919: qpq.others_group_cnt,
2029: and qph.source_system_code = qprs.source_system_code
2030: and ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') = 'N')
2031: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
2032: union all
2033: 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 */
2034: qpq.list_header_id,
2035: qpq.list_line_id list_line_id,
2036: qpq.qualifier_group_cnt,
2037: qpq.others_group_cnt,
2371: */
2372: -- Changed the cursor l_outer_qual_cur for bug#3002632
2373: CURSOR l_OUTER_QUAL_CUR IS
2374: -- Qualifier Only
2375: 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 */
2376: qpq.list_header_id,
2377: qpq.list_line_id list_line_id,
2378: qpq.qualifier_group_cnt,
2379: qpq.others_group_cnt,
2467: and qph.source_system_code = qprs.source_system_code
2468: and ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') = 'N')
2469: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
2470: union all
2471: 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 */
2472: qpq.list_header_id,
2473: qpq.list_line_id list_line_id,
2474: qpq.qualifier_group_cnt,
2475: qpq.others_group_cnt,
2565: and qph.source_system_code = qprs.source_system_code
2566: and ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') = 'N')
2567: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
2568: union all
2569: 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 */
2570: qpq.list_header_id,
2571: qpq.list_line_id list_line_id,
2572: qpq.qualifier_group_cnt,
2573: qpq.others_group_cnt,
2665: and qph.source_system_code = qprs.source_system_code
2666: and ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') = 'N')
2667: OR (qplatq.DERIVED_QUALIFIER_FLAG = 'Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
2668: union all
2669: 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 */
2670: qpq.list_header_id,
2671: qpq.list_line_id list_line_id,
2672: qpq.qualifier_group_cnt,
2673: qpq.others_group_cnt,
7611:
7612: -- julin [3805113]: bulk update for CROSS ORDER VOLUME
7613: IF (l_co_line_index_tbl.count > 0) THEN
7614: FORALL I IN l_co_line_index_tbl.first..l_co_line_index_tbl.last
7615: UPDATE /*+ index(qp_npreq_line_attrs_tmp qp_preq_line_attrs_tmp_n2) */ qp_npreq_line_attrs_tmp --upd4
7616: 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))
7617: WHERE LINE_INDEX = l_co_line_index_tbl(I)
7618: AND CONTEXT = G_PRIC_VOLUME_CONTEXT
7619: AND ATTRIBUTE IN ('PRICING_ATTRIBUTE3','PRICING_ATTRIBUTE1','PRICING_ATTRIBUTE11')
7623:
7624: -- julin [3805113]: bulk update for ITEM AMOUNT
7625: IF (l_ia_line_index_tbl.count > 0) THEN
7626: FORALL I IN l_ia_line_index_tbl.first..l_ia_line_index_tbl.last
7627: UPDATE /*+ index(qp_npreq_line_attrs_tmp qp_preq_line_attrs_tmp_n2) */ qp_npreq_line_attrs_tmp --upd3, upd5
7628: SET VALUE_FROM = l_ia_value_from_tbl(I)
7629: WHERE LINE_INDEX = l_ia_line_index_tbl(I)
7630: AND CONTEXT = G_PRIC_VOLUME_CONTEXT
7631: AND ATTRIBUTE = l_ia_attribute_tbl(I)
7854: AND list_line_id = -1
7855: AND NOT EXISTS (select /*+ NO_UNNEST
7856: leading (c)
7857: use_nl (c qpprod)
7858: index (qpprod, QP_PREQ_LINE_ATTRS_TMP_N6)
7859: index(c qp_pricing_attributes_n13) */
7860: 'x'
7861: FROM qp_npreq_line_attrs_tmp qpprod, qp_pricing_attributes c
7862: WHERE c.list_header_id = a.list_header_id -- delete header for which product does
7873: --UNION
7874: --SELECT /*+ ORDERED USE_NL(d qppric) index(d qp_pricing_attributes_n5) */ 'x'
7875: /*FROM qp_npreq_line_attrs_tmp qpprod,
7876: qp_pricing_attributes d,
7877: qp_preq_line_attrs_tmp qppric
7878: WHERE d.list_header_id = a.list_header_id -- delete header for which product and
7879: AND d.pricing_phase_id = p_pricing_phase_id -- pricing attrbutes do not match
7880: AND d.QUALIFICATION_IND in (G_YES_PROD_PRIC_HDR_QUAL_IND, G_YES_PRIC_HDR_LINE_QUAL_IND)
7881: AND d.product_attribute_context = qpprod.context
7957: WHERE a.ASK_FOR_FLAG = QP_PREQ_GRP.G_YES
7958: AND a.PRICING_PHASE_ID = p_pricing_phase_id
7959: AND a.LINE_INDEX = p_line_index
7960: AND NOT EXISTS (
7961: SELECT /*+ index(b QP_PREQ_LINE_ATTRS_TMP_N2) */ 'X' -- This can use N2 instead of N7
7962: FROM qp_npreq_line_attrs_tmp b
7963: WHERE b.LINE_INDEX = p_line_index
7964: AND b.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_UNCHANGED
7965: AND b.ATTRIBUTE_TYPE = QP_PREQ_GRP.G_QUALIFIER_TYPE
10099: */
10100:
10101: --Pricing Phase Change
10102: CURSOR l_get_multiple_exclude_cur IS
10103: SELECT /*+ ORDERED USE_NL(b) index(a qp_preq_line_attrs_tmp_N5) l_get_multiple_exclude_cur */
10104: distinct a.line_index , a.list_header_id , a.list_line_id , a.grouping_number grouping_no,
10105: a.context , a.attribute , a.setup_value_from
10106: FROM qp_npreq_line_attrs_tmp a , qp_npreq_ldets_tmp b
10107: WHERE
10777:
10778: */
10779:
10780: CURSOR l_pric_between_cur(p_datatype VARCHAR2) IS
10781: SELECT /*+ ORDERED USE_NL(ldets) index(pricing qp_preq_line_attrs_tmp_N5) l_pric_between_cur */
10782: pricing.line_detail_index,
10783: pricing.line_index,
10784: pricing.value_from,
10785: -- line group not applicable for price lists.ignore any code around line group for price lists
10814:
10815: */
10816:
10817: CURSOR l_mod_between_cur(p_datatype VARCHAR2) IS
10818: SELECT /*+ ORDERED USE_NL(ldets) index(pricing qp_preq_line_attrs_tmp_N5) l_mod_between_cur */
10819: pricing.line_detail_index,
10820: pricing.line_index,
10821: pricing.value_from,
10822: ldets.group_quantity,
10850:
10851: */
10852:
10853: CURSOR l_pric_character_between_cur IS
10854: SELECT /*+ ORDERED USE_NL(c) index(b qp_preq_line_attrs_tmp_N5) l_pric_character_between_cur */
10855: b.line_detail_index,
10856: b.line_index,
10857: b.value_from,
10858: b.setup_value_from,
10885:
10886: */
10887:
10888: CURSOR l_mod_character_between_cur IS
10889: SELECT /*+ ORDERED USE_NL(c) index(b qp_preq_line_attrs_tmp_N5) l_mod_character_between_cur */
10890: b.line_detail_index,
10891: b.line_index,
10892: b.value_from,
10893: b.setup_value_from,
11512: AND NVL(TRUNC(QCDT.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
11513: AND ( /* Vivek */
11514: (qcdt.curr_attribute_context is null /* Vivek */
11515: and not exists /* Vivek */
11516: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
11517: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1 /* Vivek */
11518: where qcdt1.curr_attribute_context IS NOT NULL /* Vivek */
11519: and qcdt1.curr_attribute_context = pa_tmp.context /* Vivek */
11520: and qcdt1.curr_attribute = pa_tmp.attribute /* Vivek */
11531: ) /* Vivek */
11532: OR /* Vivek */
11533: (qcdt.curr_attribute_context is not null /* Vivek */
11534: and qcdt.precedence =
11535: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
11536: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2 /* Vivek */
11537: where qcdt2.curr_attribute_context IS NOT NULL /* Vivek */
11538: and qcdt2.curr_attribute_context = pa_tmp1.context /* Vivek */
11539: and qcdt2.curr_attribute = pa_tmp1.attribute /* Vivek */
13045: */
13046:
13047: --Pricing Phase Change
13048: CURSOR header_level_qual_attrs_cur IS
13049: SELECT /*+ ORDERED USE_NL(b) index(a qp_preq_line_attrs_tmp_N5) header_level_qual_attrs_cur */
13050: a.LIST_HEADER_ID,
13051: a.LIST_LINE_ID,
13052: a.LINE_INDEX,
13053: a.GROUPING_NUMBER,
13074:
13075: */
13076:
13077: CURSOR line_level_qual_attrs_cur2 IS
13078: SELECT /*+ ORDERED USE_NL(b) index(a qp_preq_line_attrs_tmp_N5) line_level_qual_attrs_cur2 */
13079: a.LIST_HEADER_ID,
13080: a.LIST_LINE_ID,
13081: a.LINE_INDEX,
13082: a.GROUPING_NUMBER,
13293:
13294: */
13295: --Pricing Phase Change
13296: CURSOR product_level_attrs_cur IS
13297: 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
13298: a.LIST_HEADER_ID , a.LIST_LINE_ID , a.LINE_INDEX,
13299: SUM(distinct(decode(a.attribute_type,G_PRODUCT_TYPE,1,0))) +
13300: COUNT(distinct(decode(a.attribute_type,G_PRICING_TYPE, a.context || ' ' || a.attribute || ' ' || setup_value_from ,NULL)))
13301: NO_OF_PATTS_IN_GRP -- Bug No: 7129919
13447: SELECT 1
13448: FROM DUAL
13449: WHERE EXISTS
13450: (SELECT 'x'
13451: FROM QP_PREQ_LINE_ATTRS_TMP
13452: WHERE LINE_INDEX = p_line_index
13453: AND LIST_HEADER_ID = p_list_header_id
13454: AND LIST_LINE_ID = p_list_line_id
13455: AND PRICING_STATUS_CODE = G_DELETED_BETWEEN
14347: --[julin/pricebook] added hint
14348: --[julin/5223502] price_list_header_id directly passed in via lines_tmp has precedence over sourced value
14349: UPDATE /*+ dynamic_sampling(1) */ qp_npreq_lines_tmp a
14350: SET (price_list_header_id,validated_flag) =
14351: (SELECT /*+ index(b QP_PREQ_LINE_ATTRS_TMP_N2) */ distinct
14352: CASE WHEN (a.PRICE_LIST_HEADER_ID < 0) THEN qp_number.canonical_to_number(value_from)
14353: ELSE a.PRICE_LIST_HEADER_ID
14354: END price_list_header_id,
14355: validated_flag
14710: AND QPLAT_PROD.PRICING_STATUS_CODE = G_STATUS_UNCHANGED
14711: AND QPLINES.PROCESSED_CODE = G_STS_LHS_NOT_FOUND
14712: AND (instr(QPLINES.PROCESS_STATUS,'FREEGOOD')=0) -- bug 2693700
14713: UNION ALL
14714: 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 */
14715: QPLAT_PROD.LINE_INDEX,
14716: QPL.LIST_LINE_ID,
14717: QPL.ARITHMETIC_OPERATOR,
14718: QPL.OPERAND,
14978: AND G_QUALIFY_SECONDARY = G_YES
14979: AND ((nvl(QPLAT_Q.DERIVED_QUALIFIER_FLAG,'N')='N')
14980: OR (QPLAT_Q.DERIVED_QUALIFIER_FLAG = 'Y' AND QPSQ.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
14981: UNION ALL
14982: 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 */
14983: QPLAT_PROD.LINE_INDEX,
14984: QPL.LIST_LINE_ID,
14985: QPL.ARITHMETIC_OPERATOR,
14986: QPL.OPERAND,
15249: AND NVL(TRUNC(QCDT.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
15250: AND ( /* Vivek */
15251: (qcdt.curr_attribute_context is null /* Vivek */
15252: and not exists /* Vivek */
15253: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
15254: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1 /* Vivek */
15255: where qcdt1.curr_attribute_context IS NOT NULL /* Vivek */
15256: and qcdt1.curr_attribute_context = pa_tmp.context /* Vivek */
15257: and qcdt1.curr_attribute = pa_tmp.attribute /* Vivek */
15276: AND QPLINES.PRICING_EFFECTIVE_DATE BETWEEN /* Vivek */
15277: NVL(TRUNC(QCDT3.START_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
15278: AND NVL(TRUNC(QCDT3.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
15279: and qcdt3.precedence =
15280: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
15281: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2 /* Vivek */
15282: where qcdt2.curr_attribute_context IS NOT NULL /* Vivek */
15283: and qcdt2.curr_attribute_context = pa_tmp1.context /* Vivek */
15284: and qcdt2.curr_attribute = pa_tmp1.attribute /* Vivek */
15310: AND QPLAT_PROD.PRICING_STATUS_CODE = G_STATUS_UNCHANGED
15311: AND QPLINES.PROCESSED_CODE = G_STS_LHS_NOT_FOUND
15312: AND (instr(QPLINES.PROCESS_STATUS,'FREEGOOD')=0) -- bug 2693700
15313: UNION ALL
15314: 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 */
15315: QPLAT_PROD.LINE_INDEX,
15316: QPL.LIST_LINE_ID,
15317: QPL.ARITHMETIC_OPERATOR,
15318: QPL.OPERAND,
15440: AND NVL(TRUNC(QCDT.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
15441: AND ( /* Vivek */
15442: (qcdt.curr_attribute_context is null /* Vivek */
15443: and not exists /* Vivek */
15444: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
15445: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1 /* Vivek */
15446: where qcdt1.curr_attribute_context IS NOT NULL /* Vivek */
15447: and qcdt1.curr_attribute_context = pa_tmp.context /* Vivek */
15448: and qcdt1.curr_attribute = pa_tmp.attribute /* Vivek */
15467: AND QPLINES.PRICING_EFFECTIVE_DATE BETWEEN /* Vivek */
15468: NVL(TRUNC(QCDT3.START_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
15469: AND NVL(TRUNC(QCDT3.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
15470: and qcdt3.precedence =
15471: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
15472: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2 /* Vivek */
15473: where qcdt2.curr_attribute_context IS NOT NULL /* Vivek */
15474: and qcdt2.curr_attribute_context = pa_tmp1.context /* Vivek */
15475: and qcdt2.curr_attribute = pa_tmp1.attribute /* Vivek */
15614: AND NVL(TRUNC(QCDT.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
15615: AND ( /* Vivek */
15616: (qcdt.curr_attribute_context is null /* Vivek */
15617: and not exists /* Vivek */
15618: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
15619: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1 /* Vivek */
15620: where qcdt1.curr_attribute_context IS NOT NULL /* Vivek */
15621: and qcdt1.curr_attribute_context = pa_tmp.context /* Vivek */
15622: and qcdt1.curr_attribute = pa_tmp.attribute /* Vivek */
15641: AND QPLINES.PRICING_EFFECTIVE_DATE BETWEEN /* Vivek */
15642: NVL(TRUNC(QCDT3.START_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
15643: AND NVL(TRUNC(QCDT3.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
15644: and qcdt3.precedence =
15645: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
15646: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2 /* Vivek */
15647: where qcdt2.curr_attribute_context IS NOT NULL /* Vivek */
15648: and qcdt2.curr_attribute_context = pa_tmp1.context /* Vivek */
15649: and qcdt2.curr_attribute = pa_tmp1.attribute /* Vivek */
15698: AND G_QUALIFY_SECONDARY = G_YES
15699: AND ((nvl(QPLAT_Q.DERIVED_QUALIFIER_FLAG,'N')='N')
15700: OR (QPLAT_Q.DERIVED_QUALIFIER_FLAG = 'Y' AND QPSQ.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
15701: UNION ALL
15702: 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 */
15703: QPLAT_PROD.LINE_INDEX,
15704: QPL.LIST_LINE_ID,
15705: QPL.ARITHMETIC_OPERATOR,
15706: QPL.OPERAND,
15825: AND NVL(TRUNC(QCDT.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
15826: AND ( /* Vivek */
15827: (qcdt.curr_attribute_context is null /* Vivek */
15828: and not exists /* Vivek */
15829: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
15830: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1 /* Vivek */
15831: where qcdt1.curr_attribute_context IS NOT NULL /* Vivek */
15832: and qcdt1.curr_attribute_context = pa_tmp.context /* Vivek */
15833: and qcdt1.curr_attribute = pa_tmp.attribute /* Vivek */
15852: AND QPLINES.PRICING_EFFECTIVE_DATE BETWEEN /* Vivek */
15853: NVL(TRUNC(QCDT3.START_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
15854: AND NVL(TRUNC(QCDT3.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
15855: and qcdt3.precedence =
15856: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
15857: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2 /* Vivek */
15858: where qcdt2.curr_attribute_context IS NOT NULL /* Vivek */
15859: and qcdt2.curr_attribute_context = pa_tmp1.context /* Vivek */
15860: and qcdt2.curr_attribute = pa_tmp1.attribute /* Vivek */
16917: AND QPLAT_PROD.LINE_INDEX = QPLINES.LINE_INDEX
16918: AND QPL.PRICING_PHASE_ID = G_PRICE_LIST_PHASE_ID
16919: AND (instr(QPLINES.PROCESS_STATUS,'FREEGOOD')=0) -- bug 2693700
16920: UNION ALL
16921: 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 */
16922: QPLAT_PROD.LINE_INDEX,
16923: QPL.LIST_LINE_ID,
16924: QPL.ARITHMETIC_OPERATOR,
16925: QPL.OPERAND,
17145: AND NVL(TRUNC(QCDT.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
17146: AND ( /* Vivek */
17147: (qcdt.curr_attribute_context is null /* Vivek */
17148: and not exists /* Vivek */
17149: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
17150: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1 /* Vivek */
17151: where qcdt1.curr_attribute_context IS NOT NULL /* Vivek */
17152: and qcdt1.curr_attribute_context = pa_tmp.context /* Vivek */
17153: and qcdt1.curr_attribute = pa_tmp.attribute /* Vivek */
17164: ) /* Vivek */
17165: OR /* Vivek */
17166: (qcdt.curr_attribute_context is not null /* Vivek */
17167: and qcdt.precedence =
17168: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
17169: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2 /* Vivek */
17170: where qcdt2.curr_attribute_context IS NOT NULL /* Vivek */
17171: and qcdt2.curr_attribute_context = pa_tmp1.context /* Vivek */
17172: and qcdt2.curr_attribute = pa_tmp1.attribute /* Vivek */
17192: AND QPLAT_PROD.LINE_INDEX = QPLINES.LINE_INDEX
17193: AND QPL.PRICING_PHASE_ID = G_PRICE_LIST_PHASE_ID
17194: AND (instr(QPLINES.PROCESS_STATUS,'FREEGOOD')=0) -- bug 2693700
17195: UNION ALL
17196: 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 */
17197: QPLAT_PROD.LINE_INDEX,
17198: QPL.LIST_LINE_ID,
17199: QPL.ARITHMETIC_OPERATOR,
17200: QPL.OPERAND,
17312: AND NVL(TRUNC(QCDT.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
17313: AND ( /* Vivek */
17314: (qcdt.curr_attribute_context is null /* Vivek */
17315: and not exists /* Vivek */
17316: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
17317: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1 /* Vivek */
17318: where qcdt1.curr_attribute_context IS NOT NULL /* Vivek */
17319: and qcdt1.curr_attribute_context = pa_tmp.context /* Vivek */
17320: and qcdt1.curr_attribute = pa_tmp.attribute /* Vivek */
17331: ) /* Vivek */
17332: OR /* Vivek */
17333: (qcdt.curr_attribute_context is not null /* Vivek */
17334: and qcdt.precedence =
17335: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
17336: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2 /* Vivek */
17337: where qcdt2.curr_attribute_context IS NOT NULL /* Vivek */
17338: and qcdt2.curr_attribute_context = pa_tmp1.context /* Vivek */
17339: and qcdt2.curr_attribute = pa_tmp1.attribute /* Vivek */
17403: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
17404: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
17405: */
17406: CURSOR L_LIST_QUAL_CUR IS
17407: 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 */
17408: QPLAT_PROD.LINE_INDEX,
17409: QPL.LIST_LINE_ID,
17410: QPL.ARITHMETIC_OPERATOR,
17411: QPL.OPERAND,
17529: AND (instr(QPLINES.PROCESS_STATUS,'FREEGOOD')=0) -- bug 2693700
17530: AND ((nvl(QPLAT_Q.DERIVED_QUALIFIER_FLAG,'N')='N')
17531: OR (QPLAT_Q.DERIVED_QUALIFIER_FLAG = 'Y' AND QPQ.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
17532: UNION ALL
17533: 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)
17534: INDEX(QPA QP_PRICING_ATTRIBUTES_N5) INDEX(QPLAT_PRIC qp_preq_line_attrs_tmp_N2) dynamic_sampling(1) l_list_qual_cur */
17535: QPLAT_PROD.LINE_INDEX,
17536: QPL.LIST_LINE_ID,
17537: QPL.ARITHMETIC_OPERATOR,
17530: AND ((nvl(QPLAT_Q.DERIVED_QUALIFIER_FLAG,'N')='N')
17531: OR (QPLAT_Q.DERIVED_QUALIFIER_FLAG = 'Y' AND QPQ.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
17532: UNION ALL
17533: 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)
17534: INDEX(QPA QP_PRICING_ATTRIBUTES_N5) INDEX(QPLAT_PRIC qp_preq_line_attrs_tmp_N2) dynamic_sampling(1) l_list_qual_cur */
17535: QPLAT_PROD.LINE_INDEX,
17536: QPL.LIST_LINE_ID,
17537: QPL.ARITHMETIC_OPERATOR,
17538: QPL.OPERAND,
17768: AND QPLAT_PROD.LINE_INDEX = QPLINES.LINE_INDEX
17769: --AND QPL.PRICING_PHASE_ID = G_PRICE_LIST_PHASE_ID
17770: AND (instr(QPLINES.PROCESS_STATUS,'FREEGOOD')=0) -- bug 2693700
17771: UNION ALL
17772: 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 */
17773: QPLAT_PROD.LINE_INDEX,
17774: QPL.LIST_LINE_ID,
17775: QPL.ARITHMETIC_OPERATOR,
17776: QPL.OPERAND,
17882: ORDER BY 1,2;
17883:
17884: -- vivek
17885: CURSOR L_LIST_QUAL_CUR_CURRENCY IS
17886: 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 */
17887: QPLAT_PROD.LINE_INDEX,
17888: QPL.LIST_LINE_ID,
17889: QPL.ARITHMETIC_OPERATOR,
17890: QPL.OPERAND,
17990: AND NVL(TRUNC(QCDT.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
17991: AND ( /* Vivek */
17992: (qcdt.curr_attribute_context is null /* Vivek */
17993: and not exists /* Vivek */
17994: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
17995: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1 /* Vivek */
17996: where qcdt1.curr_attribute_context IS NOT NULL /* Vivek */
17997: and qcdt1.curr_attribute_context = pa_tmp.context /* Vivek */
17998: and qcdt1.curr_attribute = pa_tmp.attribute /* Vivek */
18017: AND QPLINES.PRICING_EFFECTIVE_DATE BETWEEN /* Vivek */
18018: NVL(TRUNC(QCDT3.START_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
18019: AND NVL(TRUNC(QCDT3.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
18020: and qcdt3.precedence =
18021: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
18022: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2 /* Vivek */
18023: where qcdt2.curr_attribute_context IS NOT NULL /* Vivek */
18024: and qcdt2.curr_attribute_context = pa_tmp1.context /* Vivek */
18025: and qcdt2.curr_attribute = pa_tmp1.attribute /* Vivek */
18067: AND (instr(QPLINES.PROCESS_STATUS,'FREEGOOD')=0) -- bug 2693700
18068: AND ((nvl(QPLAT_Q.DERIVED_QUALIFIER_FLAG,'N')='N')
18069: OR (QPLAT_Q.DERIVED_QUALIFIER_FLAG = 'Y' AND QPQ.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
18070: UNION ALL
18071: 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 */
18072: QPLAT_PROD.LINE_INDEX,
18073: QPL.LIST_LINE_ID,
18074: QPL.ARITHMETIC_OPERATOR,
18075: QPL.OPERAND,
18179: AND NVL(TRUNC(QCDT.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
18180: AND ( /* Vivek */
18181: (qcdt.curr_attribute_context is null /* Vivek */
18182: and not exists /* Vivek */
18183: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
18184: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1 /* Vivek */
18185: where qcdt1.curr_attribute_context IS NOT NULL /* Vivek */
18186: and qcdt1.curr_attribute_context = pa_tmp.context /* Vivek */
18187: and qcdt1.curr_attribute = pa_tmp.attribute /* Vivek */
18206: AND QPLINES.PRICING_EFFECTIVE_DATE BETWEEN /* Vivek */
18207: NVL(TRUNC(QCDT3.START_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
18208: AND NVL(TRUNC(QCDT3.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
18209: and qcdt3.precedence =
18210: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
18211: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2 /* Vivek */
18212: where qcdt2.curr_attribute_context IS NOT NULL /* Vivek */
18213: and qcdt2.curr_attribute_context = pa_tmp1.context /* Vivek */
18214: and qcdt2.curr_attribute = pa_tmp1.attribute /* Vivek */
18369: AND NVL(TRUNC(QCDT.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
18370: AND ( /* Vivek */
18371: (qcdt.curr_attribute_context is null /* Vivek */
18372: and not exists /* Vivek */
18373: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
18374: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1 /* Vivek */
18375: where qcdt1.curr_attribute_context IS NOT NULL /* Vivek */
18376: and qcdt1.curr_attribute_context = pa_tmp.context /* Vivek */
18377: and qcdt1.curr_attribute = pa_tmp.attribute /* Vivek */
18396: AND QPLINES.PRICING_EFFECTIVE_DATE BETWEEN /* Vivek */
18397: NVL(TRUNC(QCDT3.START_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
18398: AND NVL(TRUNC(QCDT3.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
18399: and qcdt3.precedence =
18400: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
18401: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2 /* Vivek */
18402: where qcdt2.curr_attribute_context IS NOT NULL /* Vivek */
18403: and qcdt2.curr_attribute_context = pa_tmp1.context /* Vivek */
18404: and qcdt2.curr_attribute = pa_tmp1.attribute /* Vivek */
18424: AND QPLAT_PROD.LINE_INDEX = QPLINES.LINE_INDEX
18425: --AND QPL.PRICING_PHASE_ID = G_PRICE_LIST_PHASE_ID
18426: AND (instr(QPLINES.PROCESS_STATUS,'FREEGOOD')=0) -- bug 2693700
18427: UNION ALL
18428: 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 */
18429: QPLAT_PROD.LINE_INDEX,
18430: QPL.LIST_LINE_ID,
18431: QPL.ARITHMETIC_OPERATOR,
18432: QPL.OPERAND,
18540: AND NVL(TRUNC(QCDT.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
18541: AND ( /* Vivek */
18542: (qcdt.curr_attribute_context is null /* Vivek */
18543: and not exists /* Vivek */
18544: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
18545: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1 /* Vivek */
18546: where qcdt1.curr_attribute_context IS NOT NULL /* Vivek */
18547: and qcdt1.curr_attribute_context = pa_tmp.context /* Vivek */
18548: and qcdt1.curr_attribute = pa_tmp.attribute /* Vivek */
18567: AND QPLINES.PRICING_EFFECTIVE_DATE BETWEEN /* Vivek */
18568: NVL(TRUNC(QCDT3.START_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
18569: AND NVL(TRUNC(QCDT3.END_DATE_ACTIVE),QPLINES.PRICING_EFFECTIVE_DATE)/* Vivek */
18570: and qcdt3.precedence =
18571: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
18572: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2 /* Vivek */
18573: where qcdt2.curr_attribute_context IS NOT NULL /* Vivek */
18574: and qcdt2.curr_attribute_context = pa_tmp1.context /* Vivek */
18575: and qcdt2.curr_attribute = pa_tmp1.attribute /* Vivek */
18783: AND QPLINES.REQUEST_TYPE_CODE = QPRS.REQUEST_TYPE_CODE
18784: AND QPH.ACTIVE_FLAG = G_YES
18785: AND QPLAT_PROD.PRICING_STATUS_CODE = G_STATUS_UNCHANGED
18786: UNION ALL
18787: 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 */
18788: QPLINES.LINE_INDEX,
18789: QPL.LIST_LINE_ID,
18790: QPL.ARITHMETIC_OPERATOR,
18791: QPL.OPERAND,
19027: AND QPLINES.REQUEST_TYPE_CODE = QPRS.REQUEST_TYPE_CODE
19028: AND QPH.ACTIVE_FLAG = G_YES
19029: AND QPLAT_PROD.PRICING_STATUS_CODE = G_STATUS_UNCHANGED
19030: UNION ALL
19031: 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 */
19032: QPLINES.LINE_INDEX,
19033: QPL.LIST_LINE_ID,
19034: QPL.ARITHMETIC_OPERATOR,
19035: QPL.OPERAND,
21666: */
21667:
21668: 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
21669: -- Product and Qualifier Only. No pricing
21670: 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 */
21671: qpl.list_header_id,
21672: qpl.list_line_id,
21673: qpl.list_line_type_code,
21674: qplatpr.line_index,
21755: G_COUPON_ISSUE,G_TERMS_SUBSTITUTION,G_ITEM_UPGRADE)
21756: or
21757: nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) <> G_YES)
21758: union all
21759: 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 */
21760: qpl.list_header_id,
21761: qpl.list_line_id,
21762: qpl.list_line_type_code,
21763: qplatpr.line_index,
21897: */
21898: CURSOR l_prod_qual_pric_cur(p_pricing_phase_id NUMBER, p_list_header_id NUMBER, p_list_line_id NUMBER,
21899: p_line_index NUMBER) IS
21900: --Product+Qualifiers+Pricing
21901: 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 */
21902: qpl.list_header_id,
21903: qpl.list_line_id,
21904: qpl.list_line_type_code,
21905: qplatpr.line_index,
22010: G_COUPON_ISSUE,G_TERMS_SUBSTITUTION,G_ITEM_UPGRADE)
22011: or
22012: nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) <> G_YES)
22013: union all
22014: 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 */
22015: qpl.list_header_id,
22016: qpl.list_line_id,
22017: qpl.list_line_type_code,
22018: qplatpr.line_index,
22298: */
22299: CURSOR l_PROD_CUR (p_pricing_phase_id NUMBER) IS
22300: -- Only Product attributes
22301: 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
22302: /* 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 */
22303: qpl.list_header_id,
22304: qpl.list_line_id,
22305: qpl.list_line_type_code,
22306: qplatpr.line_index,
22380: OR
22381: (qpl.modifier_level_code = G_LINE_GROUP and
22382: nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) <> G_YES)) -- price book
22383: and qph.active_flag = G_YES
22384: AND ((qph.ASK_FOR_FLAG = G_YES and exists (SELECT /*+ index(la QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/pricebook]
22385: FROM qp_npreq_line_attrs_tmp la
22386: WHERE attribute_type = 'QUALIFIER'
22387: AND pricing_status_code = G_STATUS_UNCHANGED
22388: AND validated_flag = G_NO
22460: */
22461:
22462: CURSOR l_PROD_PRIC_CUR (p_pricing_phase_id NUMBER) IS
22463: --PRODUCT and PRICING attributes
22464: 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 */
22465: qpl.list_header_id,
22466: qpl.list_line_id,
22467: qpl.list_line_type_code,
22468: qplatpr.line_index,
22547: OR
22548: (qpl.modifier_level_code = G_LINE_GROUP and
22549: nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) <> G_YES)) -- price book
22550: and qph.active_flag = G_YES
22551: AND ((qph.ASK_FOR_FLAG = G_YES and exists (SELECT /*+ index(la QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/pricebook]
22552: FROM qp_npreq_line_attrs_tmp la
22553: WHERE attribute_type = 'QUALIFIER'
22554: AND pricing_status_code = G_STATUS_UNCHANGED
22555: AND validated_flag = G_NO
22669: --AND ((nvl(G_SECURITY_CONTROL, 'OFF') = 'ON' AND G_CURRENT_USER_OP_UNIT = QPH.orig_org_id) OR -- begin security
22670: -- (nvl(G_SECURITY_CONTROL, 'OFF') = 'ON' AND nvl(QPH.global_flag,'Y') = 'Y') OR
22671: -- nvl(G_SECURITY_CONTROL, 'OFF') = 'OFF' ) -- end security
22672: and qph.active_flag=G_YES
22673: AND ((qph.ASK_FOR_FLAG = G_YES and exists (SELECT /*+ index(la QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/pricebook]
22674: FROM qp_npreq_line_attrs_tmp la
22675: WHERE attribute_type = 'QUALIFIER'
22676: AND pricing_status_code = G_STATUS_UNCHANGED
22677: AND validated_flag = G_NO
22746:
22747:
22748: -- Pricing Phase Change
22749: CURSOR l_get_excluded_prods_cur(p_pricing_phase_id NUMBER) IS
22750: 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 /
22751: b.list_header_id,
22752: b.list_line_id,
22753: c.line_index,
22754: c.validated_flag,
22820:
22821: --hw
22822:
22823: CURSOR l_get_line_index_for_quals IS
22824: SELECT /*+ ORDERED USE_NL(c a) index(a qp_preq_line_attrs_tmp_n2) l_get_line_index_for_quals */distinct
22825: c.LIST_HEADER_ID,
22826: c.LIST_LINE_ID,
22827: c.QUALIFIER_ATTRIBUTE_CONTEXT,
22828: c.QUALIFIER_ATTRIBUTE,
22873: AND a.PRICING_STATUS_CODE = G_STATUS_UNCHANGED
22874: AND c.PRICING_STATUS_CODE = G_STATUS_UNCHANGED
22875: AND c.PRICING_PHASE_ID = p_pricing_phase_id
22876: AND a.LINE_INDEX = b.LINE_INDEX
22877: AND ((c.ASK_FOR_FLAG = G_YES and exists (SELECT /*+ index(la QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/pricebook]
22878: FROM qp_npreq_line_attrs_tmp la
22879: WHERE attribute_type = 'QUALIFIER'
22880: AND pricing_status_code = G_STATUS_UNCHANGED
22881: AND validated_flag = G_NO
25377: UPDATE qp_npreq_ldets_tmp a
25378: SET pricing_status_code = 'D_EXCLUDER'
25379: WHERE pricing_phase_id = p_pricing_phase_id
25380: AND pricing_status_code = G_STATUS_NEW
25381: AND EXISTS ( SELECT/*+ ORDERED USE_NL(c) index(c qp_preq_line_attrs_tmp_N2) index(b QP_PRICING_ATTRIBUTES_N2) l_excl_cur */
25382: 'x'
25383: FROM qp_pricing_attributes b,
25384: qp_npreq_line_attrs_tmp c
25385: WHERE b.list_line_id = a.created_from_list_line_id
25555: -- 3. Split into unions ('=', 'BETWEEN', 'NOT =') for better use of indexes
25556: CURSOR l_prod_qual_cur (p_pricing_phase_id NUMBER) IS
25557: -- Product and Qualifier Only. No pricing
25558: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
25559: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
25560: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
25561: INDEX(QPQ QP_QUALIFIERS_N4)
25562: l_prod_qual_cur_pl */
25563: qpl.list_header_id,
25556: CURSOR l_prod_qual_cur (p_pricing_phase_id NUMBER) IS
25557: -- Product and Qualifier Only. No pricing
25558: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
25559: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
25560: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
25561: INDEX(QPQ QP_QUALIFIERS_N4)
25562: l_prod_qual_cur_pl */
25563: qpl.list_header_id,
25564: qpl.list_line_id,
25663: AND ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') ='N' )
25664: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
25665: UNION ALL
25666: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
25667: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
25668: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
25669: INDEX(QPQ QP_QUALIFIERS_N5)
25670: l_prod_qual_cur_pl */
25671: qpl.list_header_id,
25664: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
25665: UNION ALL
25666: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
25667: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
25668: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
25669: INDEX(QPQ QP_QUALIFIERS_N5)
25670: l_prod_qual_cur_pl */
25671: qpl.list_header_id,
25672: qpl.list_line_id,
25771: AND ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') ='N' )
25772: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
25773: UNION ALL
25774: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
25775: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
25776: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
25777: INDEX(QPQ QP_QUALIFIERS_N6)
25778: l_prod_qual_cur_pl */
25779: qpl.list_header_id,
25772: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
25773: UNION ALL
25774: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
25775: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
25776: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
25777: INDEX(QPQ QP_QUALIFIERS_N6)
25778: l_prod_qual_cur_pl */
25779: qpl.list_header_id,
25780: qpl.list_line_id,
25892: -- 3. Split into unions ('=', 'BETWEEN', 'NOT =') for better use of indexes
25893: CURSOR l_prod_qual_cur_currency (p_pricing_phase_id NUMBER) IS
25894: -- Product and Qualifier Only. No pricing
25895: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
25896: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
25897: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
25898: INDEX(QPQ QP_QUALIFIERS_N4)
25899: l_prod_qual_cur_pl */
25900: qpl.list_header_id,
25893: CURSOR l_prod_qual_cur_currency (p_pricing_phase_id NUMBER) IS
25894: -- Product and Qualifier Only. No pricing
25895: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
25896: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
25897: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
25898: INDEX(QPQ QP_QUALIFIERS_N4)
25899: l_prod_qual_cur_pl */
25900: qpl.list_header_id,
25901: qpl.list_line_id,
25978: nvl(trunc(qcdt.end_date_active),qplines.pricing_effective_date)
25979: AND (
25980: (qcdt.curr_attribute_context is null
25981: and not exists
25982: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
25983: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1
25984: where qcdt1.curr_attribute_context IS NOT NULL
25985: and qcdt1.curr_attribute_context = pa_tmp.context
25986: and qcdt1.curr_attribute = pa_tmp.attribute
25995: and nvl(trunc(qcdt1.end_date_active),qplines.pricing_effective_date)))
25996: OR
25997: (qcdt.curr_attribute_context is not null
25998: and qcdt.precedence =
25999: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
26000: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2
26001: where qcdt2.curr_attribute_context IS NOT NULL
26002: and qcdt2.curr_attribute_context = pa_tmp1.context
26003: and qcdt2.curr_attribute = pa_tmp1.attribute
26047: AND ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') ='N' )
26048: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
26049: UNION ALL
26050: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
26051: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
26052: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
26053: INDEX(QPQ QP_QUALIFIERS_N5)
26054: l_prod_qual_cur_pl */
26055: qpl.list_header_id,
26048: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
26049: UNION ALL
26050: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
26051: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
26052: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
26053: INDEX(QPQ QP_QUALIFIERS_N5)
26054: l_prod_qual_cur_pl */
26055: qpl.list_header_id,
26056: qpl.list_line_id,
26133: nvl(trunc(qcdt.end_date_active),qplines.pricing_effective_date)
26134: AND (
26135: (qcdt.curr_attribute_context is null
26136: and not exists
26137: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
26138: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1
26139: where qcdt1.curr_attribute_context IS NOT NULL
26140: and qcdt1.curr_attribute_context = pa_tmp.context
26141: and qcdt1.curr_attribute = pa_tmp.attribute
26150: and nvl(trunc(qcdt1.end_date_active),qplines.pricing_effective_date)))
26151: OR
26152: (qcdt.curr_attribute_context is not null
26153: and qcdt.precedence =
26154: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
26155: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2
26156: where qcdt2.curr_attribute_context IS NOT NULL
26157: and qcdt2.curr_attribute_context = pa_tmp1.context
26158: and qcdt2.curr_attribute = pa_tmp1.attribute
26202: AND ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') ='N' )
26203: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
26204: UNION ALL
26205: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
26206: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
26207: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
26208: INDEX(QPQ QP_QUALIFIERS_N6)
26209: l_prod_qual_cur_pl */
26210: qpl.list_header_id,
26203: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
26204: UNION ALL
26205: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
26206: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
26207: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
26208: INDEX(QPQ QP_QUALIFIERS_N6)
26209: l_prod_qual_cur_pl */
26210: qpl.list_header_id,
26211: qpl.list_line_id,
26288: nvl(trunc(qcdt.end_date_active),qplines.pricing_effective_date)
26289: AND (
26290: (qcdt.curr_attribute_context is null
26291: and not exists
26292: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
26293: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1
26294: where qcdt1.curr_attribute_context IS NOT NULL
26295: and qcdt1.curr_attribute_context = pa_tmp.context
26296: and qcdt1.curr_attribute = pa_tmp.attribute
26305: and nvl(trunc(qcdt1.end_date_active),qplines.pricing_effective_date)))
26306: OR
26307: (qcdt.curr_attribute_context is not null
26308: and qcdt.precedence =
26309: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
26310: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2
26311: where qcdt2.curr_attribute_context IS NOT NULL
26312: and qcdt2.curr_attribute_context = pa_tmp1.context
26313: and qcdt2.curr_attribute = pa_tmp1.attribute
26363: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
26364: ORDER BY 4,2;
26365:
26366: --Added INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5) for 3259932
26367: --[julin/4708044] added index(qplapr QP_PREQ_LINE_ATTRS_TMP_N1), use_nl(qprs)
26368: CURSOR l_prod_qual_pric_cur(p_pricing_phase_id NUMBER) IS
26369: --Product+Qualifiers+Pricing
26370: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
26371: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
26367: --[julin/4708044] added index(qplapr QP_PREQ_LINE_ATTRS_TMP_N1), use_nl(qprs)
26368: CURSOR l_prod_qual_pric_cur(p_pricing_phase_id NUMBER) IS
26369: --Product+Qualifiers+Pricing
26370: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
26371: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
26372: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
26373: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
26374: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
26375: INDEX(QPQ QP_QUALIFIERS_N4)
26368: CURSOR l_prod_qual_pric_cur(p_pricing_phase_id NUMBER) IS
26369: --Product+Qualifiers+Pricing
26370: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
26371: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
26372: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
26373: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
26374: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
26375: INDEX(QPQ QP_QUALIFIERS_N4)
26376: l_prod_qual_pric_cur_pl */
26369: --Product+Qualifiers+Pricing
26370: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
26371: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
26372: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
26373: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
26374: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
26375: INDEX(QPQ QP_QUALIFIERS_N4)
26376: l_prod_qual_pric_cur_pl */
26377: qpl.list_header_id,
26495: AND ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') ='N' )
26496: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
26497: UNION ALL
26498: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
26499: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
26500: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
26501: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
26502: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
26503: INDEX(QPQ QP_QUALIFIERS_N5)
26496: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
26497: UNION ALL
26498: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
26499: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
26500: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
26501: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
26502: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
26503: INDEX(QPQ QP_QUALIFIERS_N5)
26504: l_prod_qual_pric_cur_pl */
26497: UNION ALL
26498: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
26499: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
26500: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
26501: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
26502: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
26503: INDEX(QPQ QP_QUALIFIERS_N5)
26504: l_prod_qual_pric_cur_pl */
26505: qpl.list_header_id,
26623: AND ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') ='N' )
26624: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
26625: UNION ALL
26626: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
26627: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
26628: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
26629: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
26630: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
26631: INDEX(QPQ QP_QUALIFIERS_N6)
26624: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
26625: UNION ALL
26626: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
26627: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
26628: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
26629: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
26630: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
26631: INDEX(QPQ QP_QUALIFIERS_N6)
26632: l_prod_qual_pric_cur_pl */
26625: UNION ALL
26626: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
26627: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
26628: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
26629: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
26630: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
26631: INDEX(QPQ QP_QUALIFIERS_N6)
26632: l_prod_qual_pric_cur_pl */
26633: qpl.list_header_id,
26757: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
26758: ORDER BY 4,2;
26759:
26760: --Added INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5) for 3259932
26761: --[julin/4708044] added index(qplapr QP_PREQ_LINE_ATTRS_TMP_N1), use_nl(qprs)
26762: --[julin/5007789]
26763: -- 1. Performing line filter first.
26764: -- 2. Performing qualifier filter before product filter
26765: -- 3. Split into unions ('=', 'BETWEEN', 'NOT =') for better use of indexes
26765: -- 3. Split into unions ('=', 'BETWEEN', 'NOT =') for better use of indexes
26766: CURSOR l_prod_qual_pric_cur_currency(p_pricing_phase_id NUMBER) IS
26767: --Product+Qualifiers+Pricing
26768: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
26769: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
26770: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
26771: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
26772: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
26773: INDEX(QPQ QP_QUALIFIERS_N4)
26766: CURSOR l_prod_qual_pric_cur_currency(p_pricing_phase_id NUMBER) IS
26767: --Product+Qualifiers+Pricing
26768: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
26769: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
26770: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
26771: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
26772: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
26773: INDEX(QPQ QP_QUALIFIERS_N4)
26774: l_prod_qual_pric_cur_pl */
26767: --Product+Qualifiers+Pricing
26768: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
26769: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
26770: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
26771: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
26772: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
26773: INDEX(QPQ QP_QUALIFIERS_N4)
26774: l_prod_qual_pric_cur_pl */
26775: qpl.list_header_id,
26862: and nvl(trunc(qcdt.end_date_active),qplines.pricing_effective_date)
26863: AND (
26864: (qcdt.curr_attribute_context is null
26865: and not exists
26866: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
26867: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1
26868: where qcdt1.curr_attribute_context IS NOT NULL
26869: and qcdt1.curr_attribute_context = pa_tmp.context
26870: and qcdt1.curr_attribute = pa_tmp.attribute
26879: and nvl(trunc(qcdt1.end_date_active),qplines.pricing_effective_date)))
26880: OR
26881: (qcdt.curr_attribute_context is not null
26882: and qcdt.precedence =
26883: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
26884: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2
26885: where qcdt2.curr_attribute_context IS NOT NULL
26886: and qcdt2.curr_attribute_context = pa_tmp1.context
26887: and qcdt2.curr_attribute = pa_tmp1.attribute
26940: AND ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') ='N' )
26941: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
26942: UNION ALL
26943: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
26944: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
26945: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
26946: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
26947: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
26948: INDEX(QPQ QP_QUALIFIERS_N5)
26941: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
26942: UNION ALL
26943: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
26944: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
26945: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
26946: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
26947: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
26948: INDEX(QPQ QP_QUALIFIERS_N5)
26949: l_prod_qual_pric_cur_pl */
26942: UNION ALL
26943: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
26944: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
26945: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
26946: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
26947: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
26948: INDEX(QPQ QP_QUALIFIERS_N5)
26949: l_prod_qual_pric_cur_pl */
26950: qpl.list_header_id,
27037: and nvl(trunc(qcdt.end_date_active),qplines.pricing_effective_date)
27038: AND (
27039: (qcdt.curr_attribute_context is null
27040: and not exists
27041: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
27042: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1
27043: where qcdt1.curr_attribute_context IS NOT NULL
27044: and qcdt1.curr_attribute_context = pa_tmp.context
27045: and qcdt1.curr_attribute = pa_tmp.attribute
27054: and nvl(trunc(qcdt1.end_date_active),qplines.pricing_effective_date)))
27055: OR
27056: (qcdt.curr_attribute_context is not null
27057: and qcdt.precedence =
27058: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
27059: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2
27060: where qcdt2.curr_attribute_context IS NOT NULL
27061: and qcdt2.curr_attribute_context = pa_tmp1.context
27062: and qcdt2.curr_attribute = pa_tmp1.attribute
27115: AND ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') ='N' )
27116: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
27117: UNION ALL
27118: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
27119: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
27120: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
27121: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
27122: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
27123: INDEX(QPQ QP_QUALIFIERS_N6)
27116: OR (qplatq.DERIVED_QUALIFIER_FLAG ='Y' AND qpq.QUALIFY_HIER_DESCENDENTS_FLAG ='Y')) -- Added for TCA
27117: UNION ALL
27118: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
27119: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
27120: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
27121: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
27122: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
27123: INDEX(QPQ QP_QUALIFIERS_N6)
27124: l_prod_qual_pric_cur_pl */
27117: UNION ALL
27118: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPLATPC QPL)
27119: index(qplatpc QP_PREQ_LINE_ATTRS_TMP_N2)
27120: index(qplatq QP_PREQ_LINE_ATTRS_TMP_N1)
27121: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
27122: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
27123: INDEX(QPQ QP_QUALIFIERS_N6)
27124: l_prod_qual_pric_cur_pl */
27125: qpl.list_header_id,
27212: and nvl(trunc(qcdt.end_date_active),qplines.pricing_effective_date)
27213: AND (
27214: (qcdt.curr_attribute_context is null
27215: and not exists
27216: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
27217: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1
27218: where qcdt1.curr_attribute_context IS NOT NULL
27219: and qcdt1.curr_attribute_context = pa_tmp.context
27220: and qcdt1.curr_attribute = pa_tmp.attribute
27229: and nvl(trunc(qcdt1.end_date_active),qplines.pricing_effective_date)))
27230: OR
27231: (qcdt.curr_attribute_context is not null
27232: and qcdt.precedence =
27233: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
27234: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2
27235: where qcdt2.curr_attribute_context IS NOT NULL
27236: and qcdt2.curr_attribute_context = pa_tmp1.context
27237: and qcdt2.curr_attribute = pa_tmp1.attribute
27300: -- 1. Performing line filter first.
27301: -- 2. Merged qplatpr subquery conditions into main where clause.
27302: CURSOR l_prod_cur (p_pricing_phase_id NUMBER) IS
27303: -- Only Product attributes
27304: SELECT /*+ ORDERED USE_NL(QPLATPR QPPROD QPL QPH QPRS) index(qplatpr QP_PREQ_LINE_ATTRS_TMP_N1) l_prod_cur_pl */
27305: qpl.list_header_id,
27306: qpl.list_line_id,
27307: qpl.list_line_type_code,
27308: qplatpr.line_index,
27381: --[julin/5007789] Tuned query as follows:
27382: -- 1. Performing line filter first.
27383: CURSOR l_prod_cur_currency (p_pricing_phase_id NUMBER) IS
27384: -- Product Only.
27385: SELECT /*+ ORDERED USE_NL(QPLATPR QPPROD QPL QPH QPRS) index(qplatpr QP_PREQ_LINE_ATTRS_TMP_N1) l_prod_cur_pl */
27386: qpl.list_header_id,
27387: qpl.list_line_id,
27388: qpl.list_line_type_code,
27389: qplatpr.line_index,
27448: and nvl(trunc(qcdt.end_date_active),qplines.pricing_effective_date)
27449: AND (
27450: (qcdt.curr_attribute_context is null
27451: and not exists
27452: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
27453: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1
27454: where qcdt1.curr_attribute_context IS NOT NULL
27455: and qcdt1.curr_attribute_context = pa_tmp.context
27456: and qcdt1.curr_attribute = pa_tmp.attribute
27465: and nvl(trunc(qcdt1.end_date_active),qplines.pricing_effective_date)))
27466: OR
27467: (qcdt.curr_attribute_context is not null
27468: and qcdt.precedence =
27469: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
27470: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2
27471: where qcdt2.curr_attribute_context IS NOT NULL
27472: and qcdt2.curr_attribute_context = pa_tmp1.context
27473: and qcdt2.curr_attribute = pa_tmp1.attribute
27509: -- 2. Merged qplatpr subquery conditions into main where clause.
27510: -- 3. Merged qplatpc subquery conditions into main where clause.
27511: CURSOR l_prod_pric_cur (p_pricing_phase_id NUMBER) IS
27512: --PRODUCT and PRICING attributes
27513: 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 */
27514: qpl.list_header_id,
27515: qpl.list_line_id,
27516: qpl.list_line_type_code,
27517: qplatpr.line_index,
27609: --[julin/5007789] Tuned query as follows:
27610: -- 1. Performing line filter first.
27611: CURSOR l_prod_pric_cur_currency(p_pricing_phase_id NUMBER) IS
27612: --Product+Pricing
27613: 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 */
27614: qpl.list_header_id,
27615: qpl.list_line_id,
27616: qpl.list_line_type_code,
27617: qplatpr.line_index,
27686: nvl(trunc(qcdt.end_date_active),qplines.pricing_effective_date)
27687: AND (
27688: (qcdt.curr_attribute_context is null
27689: and not exists
27690: (select /*+ ORDERED use_nl(qcdt1) index(pa_tmp QP_PREQ_LINE_ATTRS_TMP_N1) */ 'x' --[julin/5139512/4744915]
27691: from qp_npreq_line_attrs_tmp pa_tmp, qp_currency_details qcdt1
27692: where qcdt1.curr_attribute_context IS NOT NULL
27693: and qcdt1.curr_attribute_context = pa_tmp.context
27694: and qcdt1.curr_attribute = pa_tmp.attribute
27703: and nvl(trunc(qcdt1.end_date_active),qplines.pricing_effective_date)))
27704: OR
27705: (qcdt.curr_attribute_context is not null
27706: and qcdt.precedence =
27707: (select /*+ ORDERED use_nl(qcdt2) index(pa_tmp1 QP_PREQ_LINE_ATTRS_TMP_N1) */ min(qcdt2.precedence) --[julin/5139512/4744915]
27708: from qp_npreq_line_attrs_tmp pa_tmp1, qp_currency_details qcdt2
27709: where qcdt2.curr_attribute_context IS NOT NULL
27710: and qcdt2.curr_attribute_context = pa_tmp1.context
27711: and qcdt2.curr_attribute = pa_tmp1.attribute
29473: INDX,lQP_PREQ_GRP.create_qualifier_from_list._check_modlist_cur,qp_npreq_line_attrs_tmp_N2,VALUE_FROM,6
29474: */
29475: --check if passed in price list indeed has qualified
29476: CURSOR l_check_modlist_cur(p_line_index IN PLS_INTEGER) IS
29477: SELECT /*+ ORDERED index(qplat qp_preq_line_attrs_tmp_n2) l_check_modlist_cur */ 'X'
29478: FROM qp_npreq_ldets_tmp qpld,
29479: qp_npreq_line_attrs_tmp qplat
29480: WHERE qpld.line_index = p_line_index
29481: AND qpld.pricing_phase_id = G_PRICE_LIST_PHASE_ID
29492: SELECT /*+ index(qpll QP_PREQ_LINES_TMP_N2) */ qpll.line_index
29493: FROM qp_npreq_lines_tmp qpll
29494: WHERE qpll.line_type_code <> G_ORDER_LEVEL
29495: AND NOT EXISTS (
29496: SELECT /*+ ORDERED index(qpld QP_PREQ_LDETS_TMP_N1) index(qplat qp_preq_line_attrs_tmp_n2) l_check_modlist_cur */ 'X'
29497: FROM qp_npreq_ldets_tmp qpld,
29498: qp_npreq_line_attrs_tmp qplat
29499: WHERE qpld.line_index = qpll.line_index
29500: AND qpld.pricing_phase_id = G_PRICE_LIST_PHASE_ID
31839: ,MODIFIER_LEVEL_CODE
31840: ,PRIMARY_UOM_FLAG
31841: ,REQUEST_ID
31842: ,DERIVED_QUALIFIER_FLAG)
31843: SELECT /*+ ORDERED use_nl(qpc qs hn) index(qnlat QP_PREQ_LINE_ATTRS_TMP_N2) */
31844: qnlat.LINE_INDEX
31845: ,qnlat.LINE_DETAIL_INDEX
31846: ,qnlat.ATTRIBUTE_LEVEL
31847: ,qnlat.ATTRIBUTE_TYPE
33430:
33431: --[julin/pricebook] added hint
33432: UPDATE qp_npreq_lines_tmp a
33433: SET (price_list_header_id,validated_flag) =
33434: (SELECT /*+ index(b QP_PREQ_LINE_ATTRS_TMP_N2) */ distinct qp_number.canonical_to_number(value_from),validated_flag
33435: FROM qp_npreq_line_attrs_tmp b
33436: WHERE a.line_index = b.line_index
33437: AND context = G_LIST_HEADER_CONTEXT
33438: AND attribute = G_PRICELIST_ATTRIBUTE