[Home] [Help]
1675:
1676: INDX,QP_PREQ_GRP.cache_line_qualifiers.l_outer_qual_cur,QP_LIST_HEADERS_B_N2,LIST_HEADER_ID,1
1677: */
1678: -- Changed the cursor l_outer_qual_cur for bug#3002632
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 */
1750: AND pricing_status_code = G_STATUS_UNCHANGED)
1751: or
1752: qpq.header_quals_exist_flag = G_NO) -- Pure line level qualifiers
1753: and (not exists (select 'x' --3520634 start
1754: from qp_pricing_attributes qp_pa -- if no product defined,select the header
1755: where qp_pa.list_line_id = qpq.list_line_id
1756: and rownum = 1)
1757: OR -- If product defined on line level,select headers for which product matches.
1758: exists (select /*+ ORDERED USE_NL(qpprod) */ 'x'
1755: where qp_pa.list_line_id = qpq.list_line_id
1756: and rownum = 1)
1757: OR -- If product defined on line level,select headers for which product matches.
1758: exists (select /*+ ORDERED USE_NL(qpprod) */ 'x'
1759: FROM qp_pricing_attributes c, qp_npreq_line_attrs_tmp qpprod
1760: WHERE c.list_header_id = qpq.list_header_id
1761: AND c.list_line_id = qpq.list_line_id
1762: AND c.pricing_phase_id = p_pricing_phase_id
1763: AND qpprod.context = c.product_attribute_context
1865: AND pricing_status_code = G_STATUS_UNCHANGED)
1866: or
1867: qpq.header_quals_exist_flag = G_NO)
1868: and (not exists (select 'x' --3520634 start
1869: from qp_pricing_attributes qp_pa -- if no product defined,select the header
1870: where qp_pa.list_line_id = qpq.list_line_id
1871: and rownum = 1)
1872: OR -- If product defined on line level,select headers for which product matches.
1873: exists (select /*+ ORDERED USE_NL(qpprod) */ 'x'
1870: where qp_pa.list_line_id = qpq.list_line_id
1871: and rownum = 1)
1872: OR -- If product defined on line level,select headers for which product matches.
1873: exists (select /*+ ORDERED USE_NL(qpprod) */ 'x'
1874: FROM qp_pricing_attributes c, qp_npreq_line_attrs_tmp qpprod
1875: WHERE c.list_header_id = qpq.list_header_id
1876: AND c.list_line_id = qpq.list_line_id
1877: AND c.pricing_phase_id = p_pricing_phase_id
1878: AND qpprod.context = c.product_attribute_context
1981: AND pricing_status_code = G_STATUS_UNCHANGED)
1982: or
1983: qpq.header_quals_exist_flag = G_NO)
1984: and (not exists (select 'x' --3520634 start
1985: from qp_pricing_attributes qp_pa -- if no product defined,select the header
1986: where qp_pa.list_line_id = qpq.list_line_id
1987: and rownum = 1)
1988: OR -- If product defined on line level,select headers for which product matches.
1989: exists (select /*+ ORDERED USE_NL(qpprod) */ 'x'
1986: where qp_pa.list_line_id = qpq.list_line_id
1987: and rownum = 1)
1988: OR -- If product defined on line level,select headers for which product matches.
1989: exists (select /*+ ORDERED USE_NL(qpprod) */ 'x'
1990: FROM qp_pricing_attributes c, qp_npreq_line_attrs_tmp qpprod
1991: WHERE c.list_header_id = qpq.list_header_id
1992: AND c.list_line_id = qpq.list_line_id
1993: AND c.pricing_phase_id = p_pricing_phase_id
1994: AND qpprod.context = c.product_attribute_context
2100: AND pricing_status_code = G_STATUS_UNCHANGED)
2101: or
2102: qpq.header_quals_exist_flag = G_NO)
2103: and (not exists (select 'x' --3520634 start
2104: from qp_pricing_attributes qp_pa -- if no product defined,select the header
2105: where qp_pa.list_line_id = qpq.list_line_id
2106: and rownum = 1)
2107: OR -- If product defined on line level,select headers for which product matches.
2108: exists (select /*+ ORDERED USE_NL(qpprod) */ 'x'
2105: where qp_pa.list_line_id = qpq.list_line_id
2106: and rownum = 1)
2107: OR -- If product defined on line level,select headers for which product matches.
2108: exists (select /*+ ORDERED USE_NL(qpprod) */ 'x'
2109: FROM qp_pricing_attributes c, qp_npreq_line_attrs_tmp qpprod
2110: WHERE c.list_header_id = qpq.list_header_id
2111: AND c.list_line_id = qpq.list_line_id
2112: AND c.pricing_phase_id = p_pricing_phase_id
2113: AND qpprod.context = c.product_attribute_context
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
7863: AND c.pricing_phase_id = p_pricing_phase_id -- not match
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
7863: AND c.pricing_phase_id = p_pricing_phase_id -- not match
7864: AND qpprod.context = c.product_attribute_context
7865: AND qpprod.attribute = c.product_attribute
7870: AND qpprod.attribute_type = G_PRODUCT_TYPE
7871: AND c.excluder_flag = G_NO
7872: AND rownum = 1
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
7872: AND rownum = 1
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)
10282:
10283: l_routine VARCHAR2(30) := 'Process_Line_Group';
10284:
10285: CURSOR line_grp_cur IS
10286: select /*+ ORDERED USE_NL(c a ) index(a qp_pricing_attributes_n2) line_grp_cur */
10287: b.created_from_list_line_id list_line_id , c.context , c.attribute ,
10288: sum(qp_number.canonical_to_number(c.value_from)) group_value
10289: from
10290: qp_npreq_ldets_tmp b , qp_npreq_line_attrs_tmp c , qp_pricing_attributes a
10286: select /*+ ORDERED USE_NL(c a ) index(a qp_pricing_attributes_n2) line_grp_cur */
10287: b.created_from_list_line_id list_line_id , c.context , c.attribute ,
10288: sum(qp_number.canonical_to_number(c.value_from)) group_value
10289: from
10290: qp_npreq_ldets_tmp b , qp_npreq_line_attrs_tmp c , qp_pricing_attributes a
10291: where
10292: b.modifier_level_code = G_LINE_GROUP
10293: and b.pricing_phase_id = p_pricing_phase_id
10294: and c.pricing_status_code = G_STATUS_UNCHANGED
11427: INDX,QP_PREQ_GRP.handle_break.l_price_break_detail_cur,qp_npreq_line_attrs_tmp_N2,LINE_INDEX,5
11428:
11429: INDX,QP_PREQ_GRP.handle_break.l_price_break_detail_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
11430:
11431: INDX,QP_PREQ_GRP.handle_break.l_price_break_detail_cur,QP_PRICING_ATTRIBUTES_N4,PRICING_ATTRIBUTE_CONTEXT,1
11432: INDX,QP_PREQ_GRP.handle_break.l_price_break_detail_cur,QP_PRICING_ATTRIBUTES_N4,PRICING_ATTRIBUTE,2
11433:
11434: INDX,QP_PREQ_GRP.handle_break.l_price_break_detail_cur,QP_LIST_LINES_PK,LIST_LINE_ID,1
11435: */
11428:
11429: INDX,QP_PREQ_GRP.handle_break.l_price_break_detail_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
11430:
11431: INDX,QP_PREQ_GRP.handle_break.l_price_break_detail_cur,QP_PRICING_ATTRIBUTES_N4,PRICING_ATTRIBUTE_CONTEXT,1
11432: INDX,QP_PREQ_GRP.handle_break.l_price_break_detail_cur,QP_PRICING_ATTRIBUTES_N4,PRICING_ATTRIBUTE,2
11433:
11434: INDX,QP_PREQ_GRP.handle_break.l_price_break_detail_cur,QP_LIST_LINES_PK,LIST_LINE_ID,1
11435: */
11436:
11486: FROM
11487: qp_npreq_line_attrs_tmp qplat_pricing,
11488: qp_npreq_lines_tmp qplines,
11489: qp_rltd_modifiers qprm,
11490: qp_pricing_attributes qppa,
11491: qp_list_lines qpll
11492: ,qp_list_headers_b qph /* Vivek */
11493: ,qp_currency_details qcdt /* Vivek */
11494: WHERE qprm.from_rltd_modifier_id = p_list_line_id
11617: FROM
11618: qp_npreq_line_attrs_tmp qplat_pricing,
11619: qp_npreq_lines_tmp qplines,
11620: qp_rltd_modifiers qprm,
11621: qp_pricing_attributes qppa,
11622: qp_list_lines qpll
11623: WHERE qprm.from_rltd_modifier_id = p_list_line_id
11624: AND qprm.to_rltd_modifier_id = qppa.list_line_id
11625: AND qppa.list_line_id = qpll.list_line_id
13312: and b.rltd_modifier_grp_type <> 'COUPON') -- 5439022
13313: GROUP BY a.LIST_HEADER_ID , a.LIST_LINE_ID,a.LINE_INDEX;
13314:
13315: /*
13316: INDX,QP_PREQ_GRP.perform_grouping.product_level_setup_attrs_cur,QP_PRICING_ATTRIBUTES_N2,LIST_LINE_ID,1
13317: INDX,QP_PREQ_GRP.perform_grouping.product_level_setup_attrs_cur,QP_PRICING_ATTRIBUTES_N2,EXCLUDER_FLAG,2
13318: */
13319: CURSOR product_level_setup_attrs_cur(p_list_line_id NUMBER) IS
13320: SELECT COUNT(*) NO_OF_PATTS_IN_GRP
13313: GROUP BY a.LIST_HEADER_ID , a.LIST_LINE_ID,a.LINE_INDEX;
13314:
13315: /*
13316: INDX,QP_PREQ_GRP.perform_grouping.product_level_setup_attrs_cur,QP_PRICING_ATTRIBUTES_N2,LIST_LINE_ID,1
13317: INDX,QP_PREQ_GRP.perform_grouping.product_level_setup_attrs_cur,QP_PRICING_ATTRIBUTES_N2,EXCLUDER_FLAG,2
13318: */
13319: CURSOR product_level_setup_attrs_cur(p_list_line_id NUMBER) IS
13320: SELECT COUNT(*) NO_OF_PATTS_IN_GRP
13321: FROM QP_PRICING_ATTRIBUTES a
13317: INDX,QP_PREQ_GRP.perform_grouping.product_level_setup_attrs_cur,QP_PRICING_ATTRIBUTES_N2,EXCLUDER_FLAG,2
13318: */
13319: CURSOR product_level_setup_attrs_cur(p_list_line_id NUMBER) IS
13320: SELECT COUNT(*) NO_OF_PATTS_IN_GRP
13321: FROM QP_PRICING_ATTRIBUTES a
13322: WHERE a.LIST_LINE_ID = p_list_line_id
13323: AND a.EXCLUDER_FLAG <> G_YES;
13324: --AND a.PRICING_ATTRIBUTE_CONTEXT IS NOT NULL
13325: --GROUP BY LIST_LINE_ID,ATTRIBUTE_GROUPING_NO;
14410: AND QUALIFIER_CONTEXT <> 'MODLIST'
14411: AND QUALIFIER_ATTRIBUTE <> 'QUALIFIER_ATTRIBUTE4'
14412: AND ROWNUM = 1);
14413: /*
14414: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel4,QP_PRICING_ATTRIBUTES_N6,LIST_HEADER_ID,1
14415: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel4,QP_PRICING_ATTRIBUTES_N6,QUALIFICATION_IND,2
14416: */
14417:
14418: UPDATE qp_npreq_lines_tmp a
14411: AND QUALIFIER_ATTRIBUTE <> 'QUALIFIER_ATTRIBUTE4'
14412: AND ROWNUM = 1);
14413: /*
14414: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel4,QP_PRICING_ATTRIBUTES_N6,LIST_HEADER_ID,1
14415: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel4,QP_PRICING_ATTRIBUTES_N6,QUALIFICATION_IND,2
14416: */
14417:
14418: UPDATE qp_npreq_lines_tmp a
14419: SET pricing_attrs_exist_flag = G_YES
14419: SET pricing_attrs_exist_flag = G_YES
14420: WHERE price_list_header_id IS NOT NULL
14421: AND EXISTS
14422: (SELECT 'x'
14423: FROM QP_PRICING_ATTRIBUTES
14424: WHERE LIST_HEADER_ID = a.PRICE_LIST_HEADER_ID
14425: AND QUALIFICATION_IND IN (QP_PREQ_GRP.G_YES_PROD_PRIC_HDR_QUAL_IND,QP_PREQ_GRP.G_YES_PROD_PRIC_IND)
14426: AND ROWNUM = 1) ;
14427: END IF;
14564: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE,4
14565: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,qp_npreq_line_attrs_tmp_N2,LINE_INDEX,5
14566: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,qp_npreq_line_attrs_tmp_N2,VALUE_FROM,6
14567:
14568: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
14569: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
14570: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
14571: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
14572: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
14565: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,qp_npreq_line_attrs_tmp_N2,LINE_INDEX,5
14566: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,qp_npreq_line_attrs_tmp_N2,VALUE_FROM,6
14567:
14568: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
14569: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
14570: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
14571: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
14572: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
14573: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
14566: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,qp_npreq_line_attrs_tmp_N2,VALUE_FROM,6
14567:
14568: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
14569: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
14570: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
14571: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
14572: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
14573: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
14574: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
14567:
14568: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
14569: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
14570: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
14571: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
14572: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
14573: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
14574: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
14575: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
14568: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
14569: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
14570: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
14571: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
14572: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
14573: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
14574: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
14575: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
14576:
14569: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
14570: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
14571: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
14572: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
14573: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
14574: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
14575: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
14576:
14577: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_LIST_LINES_PK,LIST_LINE_ID,1
14570: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
14571: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
14572: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
14573: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
14574: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
14575: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
14576:
14577: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_LIST_LINES_PK,LIST_LINE_ID,1
14578:
14571: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
14572: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
14573: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
14574: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
14575: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
14576:
14577: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_LIST_LINES_PK,LIST_LINE_ID,1
14578:
14579: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
14589: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,qp_npreq_line_attrs_tmp_N1,ATTRIBUTE_TYPE,2
14590: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,qp_npreq_line_attrs_tmp_N1,PRICING_STATUS_CODE,3
14591: */
14592: CURSOR L_SEC_LIST_CUR IS
14593: SELECT /*+ ORDERED USE_NL(QPQ QPLAT_PROD QPA QPL QPH) INDEX(QPA QP_PRICING_ATTRIBUTES_N5) index(qph qp_list_headers_b_n2) l_sec_list_cur */
14594: QPLAT_PROD.LINE_INDEX,
14595: QPL.LIST_LINE_ID,
14596: QPL.ARITHMETIC_OPERATOR,
14597: QPL.OPERAND,
14645: FROM
14646: qp_npreq_lines_tmp QPLINES,
14647: QP_QUALIFIERS QPQ,
14648: qp_npreq_line_attrs_tmp QPLAT_PROD,
14649: QP_PRICING_ATTRIBUTES QPA,
14650: QP_LIST_LINES QPL,
14651: QP_LIST_HEADERS_B QPH,
14652: --QP_PRICE_REQ_SOURCES QPRS
14653: QP_PRICE_REQ_SOURCES_V QPRS /*yangli*/
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,
14766: FROM
14767: qp_npreq_lines_tmp qplines,
14768: QP_QUALIFIERS qpq,
14769: qp_npreq_line_attrs_tmp qplat_prod,
14770: QP_PRICING_ATTRIBUTES qpa,
14771: qp_npreq_line_attrs_tmp qplat_pric,
14772: QP_LIST_LINES qpl,
14773: QP_LIST_HEADERS_B qph,
14774: --QP_PRICE_REQ_SOURCES qprs
14837: --AND QPL.PRICING_PHASE_ID = G_PRICE_LIST_PHASE_ID
14838: AND QPLINES.PROCESSED_CODE = G_STS_LHS_NOT_FOUND
14839: AND (instr(QPLINES.PROCESS_STATUS,'FREEGOOD')=0) -- bug 2693700
14840: UNION ALL
14841: SELECT /*+ ORDERED USE_NL(QPQ QPLAT_PROD QPA QPL QPH) INDEX(QPA QP_PRICING_ATTRIBUTES_N5) index(qph qp_list_headers_b_n2) l_sec_list_cur */
14842: QPLAT_PROD.LINE_INDEX,
14843: QPL.LIST_LINE_ID,
14844: QPL.ARITHMETIC_OPERATOR,
14845: QPL.OPERAND,
14895: QP_QUALIFIERS QPQ,
14896: QP_QUALIFIERS QPSQ,
14897: qp_npreq_line_attrs_tmp QPLAT_PROD,
14898: qp_npreq_line_attrs_tmp QPLAT_Q,
14899: QP_PRICING_ATTRIBUTES QPA,
14900: QP_LIST_LINES QPL,
14901: QP_LIST_HEADERS_B QPH,
14902: --QP_PRICE_REQ_SOURCES QPRS
14903: QP_PRICE_REQ_SOURCES_V QPRS /*yangli*/
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,
15035: qp_npreq_lines_tmp qplines,
15036: QP_QUALIFIERS qpq,
15037: QP_QUALIFIERS qpsq,
15038: qp_npreq_line_attrs_tmp qplat_prod,
15039: QP_PRICING_ATTRIBUTES qpa,
15040: qp_npreq_line_attrs_tmp qplat_pric,
15041: qp_npreq_line_attrs_tmp qplat_q,
15042: QP_LIST_LINES qpl,
15043: QP_LIST_HEADERS_B qph,
15129:
15130: -- vivek
15131: --[julin/4865213] using qp_list_headers_b_n7 instead of qp_list_headers_b_n2
15132: CURSOR L_SEC_LIST_CUR_CURRENCY IS
15133: SELECT /*+ ORDERED USE_NL(QPQ QPLAT_PROD QPA QPL QPH) INDEX(QPA QP_PRICING_ATTRIBUTES_N5) index(qph qp_list_headers_b_n7) l_sec_list_cur */
15134: QPLAT_PROD.LINE_INDEX,
15135: QPL.LIST_LINE_ID,
15136: QPL.ARITHMETIC_OPERATOR,
15137: QPL.OPERAND,
15192: FROM
15193: qp_npreq_lines_tmp QPLINES,
15194: QP_QUALIFIERS QPQ,
15195: qp_npreq_line_attrs_tmp QPLAT_PROD,
15196: QP_PRICING_ATTRIBUTES QPA,
15197: QP_LIST_LINES QPL,
15198: QP_LIST_HEADERS_B QPH,
15199: --QP_PRICE_REQ_SOURCES QPRS,
15200: QP_PRICE_REQ_SOURCES_V QPRS, /* yangli */
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,
15373: FROM
15374: qp_npreq_lines_tmp qplines,
15375: QP_QUALIFIERS qpq,
15376: qp_npreq_line_attrs_tmp qplat_prod,
15377: QP_PRICING_ATTRIBUTES qpa,
15378: qp_npreq_line_attrs_tmp qplat_pric,
15379: QP_LIST_LINES qpl,
15380: QP_LIST_HEADERS_B qph,
15381: --QP_PRICE_REQ_SOURCES qprs,
15497: --AND QPL.PRICING_PHASE_ID = G_PRICE_LIST_PHASE_ID
15498: AND QPLINES.PROCESSED_CODE = G_STS_LHS_NOT_FOUND
15499: AND (instr(QPLINES.PROCESS_STATUS,'FREEGOOD')=0) -- bug 2693700
15500: UNION ALL
15501: SELECT /*+ ORDERED USE_NL(QPQ QPLAT_PROD QPA QPL QPH) INDEX(QPA QP_PRICING_ATTRIBUTES_N5) index(qph qp_list_headers_b_n7) l_sec_list_cur */
15502: QPLAT_PROD.LINE_INDEX,
15503: QPL.LIST_LINE_ID,
15504: QPL.ARITHMETIC_OPERATOR,
15505: QPL.OPERAND,
15562: QP_QUALIFIERS QPQ,
15563: QP_QUALIFIERS QPSQ,
15564: qp_npreq_line_attrs_tmp QPLAT_PROD,
15565: qp_npreq_line_attrs_tmp QPLAT_Q,
15566: QP_PRICING_ATTRIBUTES QPA,
15567: QP_LIST_LINES QPL,
15568: QP_LIST_HEADERS_B QPH,
15569: --QP_PRICE_REQ_SOURCES QPRS,
15570: QP_PRICE_REQ_SOURCES_V QPRS, /* yangli */
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,
15762: qp_npreq_lines_tmp qplines,
15763: QP_QUALIFIERS qpq,
15764: QP_QUALIFIERS qpsq,
15765: qp_npreq_line_attrs_tmp qplat_prod,
15766: QP_PRICING_ATTRIBUTES qpa,
15767: qp_npreq_line_attrs_tmp qplat_pric,
15768: qp_npreq_line_attrs_tmp qplat_q,
15769: QP_LIST_LINES qpl,
15770: QP_LIST_HEADERS_B qph,
16795: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,qp_npreq_line_attrs_tmp_N1,LINE_INDEX,1
16796: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,qp_npreq_line_attrs_tmp_N1,ATTRIBUTE_TYPE,2
16797: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,qp_npreq_line_attrs_tmp_N1,PRICING_STATUS_CODE,3
16798:
16799: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
16800: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
16801: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
16802: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
16803: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
16796: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,qp_npreq_line_attrs_tmp_N1,ATTRIBUTE_TYPE,2
16797: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,qp_npreq_line_attrs_tmp_N1,PRICING_STATUS_CODE,3
16798:
16799: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
16800: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
16801: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
16802: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
16803: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
16804: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
16797: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,qp_npreq_line_attrs_tmp_N1,PRICING_STATUS_CODE,3
16798:
16799: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
16800: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
16801: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
16802: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
16803: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
16804: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
16805: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
16798:
16799: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
16800: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
16801: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
16802: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
16803: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
16804: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
16805: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
16806: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
16799: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
16800: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
16801: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
16802: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
16803: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
16804: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
16805: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
16806: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
16807:
16800: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
16801: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
16802: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
16803: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
16804: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
16805: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
16806: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
16807:
16808: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_LIST_LINES_PK,LIST_LINE_ID,1
16801: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
16802: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
16803: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
16804: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
16805: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
16806: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
16807:
16808: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_LIST_LINES_PK,LIST_LINE_ID,1
16809:
16802: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
16803: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
16804: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
16805: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
16806: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
16807:
16808: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_LIST_LINES_PK,LIST_LINE_ID,1
16809:
16810: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
16812: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
16813: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICE_REQ_SOURCES_PK,SOURCE_SYSTEM_CODE,2
16814: */
16815: CURSOR L_LIST_CUR IS
16816: SELECT /*+ ORDERED USE_NL(QPLAT_PROD QPA QPL QPH) index(QPA QP_PRICING_ATTRIBUTES_N5) dynamic_sampling(1) l_list_cur */
16817: QPLAT_PROD.LINE_INDEX,
16818: QPL.LIST_LINE_ID,
16819: QPL.ARITHMETIC_OPERATOR,
16820: QPL.OPERAND,
16854: ,QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
16855: FROM
16856: qp_npreq_lines_tmp QPLINES,
16857: qp_npreq_line_attrs_tmp qplat_prod,
16858: QP_PRICING_ATTRIBUTES QPA,
16859: QP_LIST_LINES QPL,
16860: QP_LIST_HEADERS_B QPH,
16861: --QP_PRICE_REQ_SOURCES qprs
16862: QP_PRICE_REQ_SOURCES_V qprs /* yangli */
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,
16959: ,QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
16960: FROM
16961: qp_npreq_lines_tmp qplines,
16962: qp_npreq_line_attrs_tmp qplat_prod,
16963: QP_PRICING_ATTRIBUTES qpa,
16964: qp_npreq_line_attrs_tmp qplat_pric,
16965: QP_LIST_LINES qpl,
16966: QP_LIST_HEADERS_B qph,
16967: --QP_PRICE_REQ_SOURCES qprs
17035: ORDER BY 1,2; --ORDER BY LINE_INDEX AND LIST LINE ID
17036:
17037: -- vivek
17038: CURSOR L_LIST_CUR_CURRENCY IS
17039: SELECT /*+ ORDERED USE_NL(QPLAT_PROD QPA QPL QPH) index(QPA QP_PRICING_ATTRIBUTES_N5) l_list_cur */
17040: QPLAT_PROD.LINE_INDEX,
17041: QPL.LIST_LINE_ID,
17042: QPL.ARITHMETIC_OPERATOR,
17043: QPL.OPERAND,
17084: ,QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
17085: FROM
17086: qp_npreq_lines_tmp QPLINES,
17087: qp_npreq_line_attrs_tmp qplat_prod,
17088: QP_PRICING_ATTRIBUTES QPA,
17089: QP_LIST_LINES QPL,
17090: QP_LIST_HEADERS_B QPH,
17091: --QP_PRICE_REQ_SOURCES qprs,
17092: QP_PRICE_REQ_SOURCES_V qprs, /* yangli */
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,
17241: ,QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
17242: FROM
17243: qp_npreq_lines_tmp qplines,
17244: qp_npreq_line_attrs_tmp qplat_prod,
17245: QP_PRICING_ATTRIBUTES qpa,
17246: qp_npreq_line_attrs_tmp qplat_pric,
17247: QP_LIST_LINES qpl,
17248: QP_LIST_HEADERS_B qph,
17249: --QP_PRICE_REQ_SOURCES qprs,
17377: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE,4
17378: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_line_attrs_tmp_N2,LINE_INDEX,5
17379: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_line_attrs_tmp_N2,VALUE_FROM,6
17380:
17381: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N6,LIST_HEADER_ID,1
17382: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N6,QUALIFICATION_IND,2
17383: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N6,PRICING_PHASE_ID,3
17384: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N6,LIST_LINE_ID,4
17385:
17378: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_line_attrs_tmp_N2,LINE_INDEX,5
17379: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_line_attrs_tmp_N2,VALUE_FROM,6
17380:
17381: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N6,LIST_HEADER_ID,1
17382: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N6,QUALIFICATION_IND,2
17383: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N6,PRICING_PHASE_ID,3
17384: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N6,LIST_LINE_ID,4
17385:
17386: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_line_attrs_tmp_N1,LINE_INDEX,1
17379: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_line_attrs_tmp_N2,VALUE_FROM,6
17380:
17381: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N6,LIST_HEADER_ID,1
17382: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N6,QUALIFICATION_IND,2
17383: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N6,PRICING_PHASE_ID,3
17384: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N6,LIST_LINE_ID,4
17385:
17386: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_line_attrs_tmp_N1,LINE_INDEX,1
17387: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_line_attrs_tmp_N1,ATTRIBUTE_TYPE,2
17380:
17381: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N6,LIST_HEADER_ID,1
17382: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N6,QUALIFICATION_IND,2
17383: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N6,PRICING_PHASE_ID,3
17384: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N6,LIST_LINE_ID,4
17385:
17386: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_line_attrs_tmp_N1,LINE_INDEX,1
17387: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_line_attrs_tmp_N1,ATTRIBUTE_TYPE,2
17388: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_line_attrs_tmp_N1,PRICING_STATUS_CODE,3
17393:
17394: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
17395: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICE_REQ_SOURCES_PK,SOURCE_SYSTEM_CODE,2
17396:
17397: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
17398: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
17399: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
17400: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
17401: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
17394: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
17395: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICE_REQ_SOURCES_PK,SOURCE_SYSTEM_CODE,2
17396:
17397: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
17398: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
17399: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
17400: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
17401: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
17402: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
17395: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICE_REQ_SOURCES_PK,SOURCE_SYSTEM_CODE,2
17396:
17397: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
17398: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
17399: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
17400: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
17401: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
17402: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
17403: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
17396:
17397: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
17398: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
17399: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
17400: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
17401: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
17402: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
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
17397: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
17398: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
17399: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
17400: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
17401: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
17402: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
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: */
17398: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
17399: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
17400: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
17401: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
17402: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
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
17399: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
17400: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
17401: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
17402: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
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 */
17400: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
17401: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
17402: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
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,
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,
17459: qp_npreq_lines_tmp QPLINES,
17460: QP_QUALIFIERS QPQ,
17461: qp_npreq_line_attrs_tmp QPLAT_Q,
17462: qp_npreq_line_attrs_tmp QPLAT_PROD,
17463: QP_PRICING_ATTRIBUTES QPA,
17464: QP_LIST_LINES QPL,
17465: QP_LIST_HEADERS_B QPH,
17466: --QP_PRICE_REQ_SOURCES QPRS
17467: QP_PRICE_REQ_SOURCES_V QPRS /* yangli */
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,
17586: qp_npreq_lines_tmp QPLINES,
17587: QP_QUALIFIERS QPQ,
17588: qp_npreq_line_attrs_tmp QPLAT_Q,
17589: qp_npreq_line_attrs_tmp QPLAT_PROD,
17590: QP_PRICING_ATTRIBUTES QPA,
17591: qp_npreq_line_attrs_tmp QPLAT_PRIC,
17592: QP_LIST_LINES QPL,
17593: QP_LIST_HEADERS_B QPH,
17594: --QP_PRICE_REQ_SOURCES QPRS
17666: AND (instr(QPLINES.PROCESS_STATUS,'FREEGOOD')=0) -- bug 2693700
17667: AND ((nvl(QPLAT_Q.DERIVED_QUALIFIER_FLAG,'N')='N')
17668: OR (QPLAT_Q.DERIVED_QUALIFIER_FLAG = 'Y' AND QPQ.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
17669: UNION ALL
17670: SELECT /*+ ORDERED USE_NL(QPLINES QPLAT_PROD QPA QPL QPH) index(QPA QP_PRICING_ATTRIBUTES_N5) dynamic_sampling(1) l_list_cur */
17671: QPLAT_PROD.LINE_INDEX,
17672: QPL.LIST_LINE_ID,
17673: QPL.ARITHMETIC_OPERATOR,
17674: QPL.OPERAND,
17720: ,QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
17721: FROM
17722: qp_npreq_lines_tmp QPLINES,
17723: qp_npreq_line_attrs_tmp qplat_prod,
17724: QP_PRICING_ATTRIBUTES QPA,
17725: QP_LIST_LINES QPL,
17726: QP_LIST_HEADERS_B QPH,
17727: --QP_PRICE_REQ_SOURCES qprs
17728: QP_PRICE_REQ_SOURCES_V qprs /* yangli */
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,
17822: ,QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
17823: FROM
17824: qp_npreq_lines_tmp qplines,
17825: qp_npreq_line_attrs_tmp qplat_prod,
17826: QP_PRICING_ATTRIBUTES qpa,
17827: qp_npreq_line_attrs_tmp qplat_pric,
17828: QP_LIST_LINES qpl,
17829: QP_LIST_HEADERS_B qph,
17830: --QP_PRICE_REQ_SOURCES qprs
17945: qp_npreq_lines_tmp QPLINES,
17946: QP_QUALIFIERS QPQ,
17947: qp_npreq_line_attrs_tmp QPLAT_Q,
17948: qp_npreq_line_attrs_tmp QPLAT_PROD,
17949: QP_PRICING_ATTRIBUTES QPA,
17950: QP_LIST_LINES QPL,
17951: QP_LIST_HEADERS_B QPH,
17952: --QP_PRICE_REQ_SOURCES QPRS,
17953: QP_PRICE_REQ_SOURCES_V QPRS, /* yangli */
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,
18130: qp_npreq_lines_tmp QPLINES,
18131: QP_QUALIFIERS QPQ,
18132: qp_npreq_line_attrs_tmp QPLAT_Q,
18133: qp_npreq_line_attrs_tmp QPLAT_PROD,
18134: QP_PRICING_ATTRIBUTES QPA,
18135: qp_npreq_line_attrs_tmp QPLAT_PRIC,
18136: QP_LIST_LINES QPL,
18137: QP_LIST_HEADERS_B QPH,
18138: --QP_PRICE_REQ_SOURCES QPRS,
18261: AND (instr(QPLINES.PROCESS_STATUS,'FREEGOOD')=0) -- bug 2693700
18262: AND ((nvl(QPLAT_Q.DERIVED_QUALIFIER_FLAG,'N')='N')
18263: OR (QPLAT_Q.DERIVED_QUALIFIER_FLAG = 'Y' AND QPQ.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
18264: UNION ALL
18265: SELECT /*+ ORDERED USE_NL(QPLINES QPLAT_PROD QPA QPL QPH) index(QPA QP_PRICING_ATTRIBUTES_N5) l_list_cur */
18266: QPLAT_PROD.LINE_INDEX,
18267: QPL.LIST_LINE_ID,
18268: QPL.ARITHMETIC_OPERATOR,
18269: QPL.OPERAND,
18322: ,QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
18323: FROM
18324: qp_npreq_lines_tmp QPLINES,
18325: qp_npreq_line_attrs_tmp qplat_prod,
18326: QP_PRICING_ATTRIBUTES QPA,
18327: QP_LIST_LINES QPL,
18328: QP_LIST_HEADERS_B QPH,
18329: --QP_PRICE_REQ_SOURCES qprs,
18330: QP_PRICE_REQ_SOURCES_V qprs, /* yangli */
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,
18485: ,QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
18486: FROM
18487: qp_npreq_lines_tmp qplines,
18488: qp_npreq_line_attrs_tmp qplat_prod,
18489: QP_PRICING_ATTRIBUTES qpa,
18490: qp_npreq_line_attrs_tmp qplat_pric,
18491: QP_LIST_LINES qpl,
18492: QP_LIST_HEADERS_B qph,
18493: --QP_PRICE_REQ_SOURCES qprs,
18648:
18649: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
18650: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,qp_npreq_lines_tmp_N1,LINE_TYPE_CODE,2
18651:
18652: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
18653: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
18654: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
18655: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
18656: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
18649: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
18650: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,qp_npreq_lines_tmp_N1,LINE_TYPE_CODE,2
18651:
18652: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
18653: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
18654: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
18655: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
18656: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
18657: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
18650: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,qp_npreq_lines_tmp_N1,LINE_TYPE_CODE,2
18651:
18652: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
18653: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
18654: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
18655: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
18656: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
18657: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
18658: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
18651:
18652: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
18653: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
18654: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
18655: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
18656: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
18657: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
18658: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
18659:
18652: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
18653: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
18654: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
18655: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
18656: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
18657: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
18658: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
18659:
18660: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
18653: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
18654: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
18655: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
18656: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
18657: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
18658: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
18659:
18660: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
18661: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICE_REQ_SOURCES_PK,SOURCE_SYSTEM_CODE,2
18654: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
18655: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
18656: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
18657: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
18658: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
18659:
18660: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
18661: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICE_REQ_SOURCES_PK,SOURCE_SYSTEM_CODE,2
18662:
18661: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICE_REQ_SOURCES_PK,SOURCE_SYSTEM_CODE,2
18662:
18663: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_LIST_LINES_PK,LIST_LINE_ID,1
18664:
18665: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N4,PRICING_ATTRIBUTE_CONTEXT,1
18666: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N4,PRICING_ATTRIBUTE,2
18667: */
18668:
18669: CURSOR L_OTHER_LIST_CUR (L_LINE_INDEX PLS_INTEGER,
18662:
18663: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_LIST_LINES_PK,LIST_LINE_ID,1
18664:
18665: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N4,PRICING_ATTRIBUTE_CONTEXT,1
18666: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N4,PRICING_ATTRIBUTE,2
18667: */
18668:
18669: CURSOR L_OTHER_LIST_CUR (L_LINE_INDEX PLS_INTEGER,
18670: L_LIST_HEADER_ID NUMBER,
18668:
18669: CURSOR L_OTHER_LIST_CUR (L_LINE_INDEX PLS_INTEGER,
18670: L_LIST_HEADER_ID NUMBER,
18671: P_FREEZE_OVERRIDE_FLAG VARCHAR2)IS
18672: SELECT /*+ ORDERED USE_NL(QPLAT_PROD QPA QPH QPL QPRS) index(QPA QP_PRICING_ATTRIBUTES_N5) index(qph qp_list_headers_b_n7) l_other_list_cur */
18673: QPLINES.LINE_INDEX,
18674: QPL.LIST_LINE_ID,
18675: QPL.ARITHMETIC_OPERATOR,
18676: QPL.OPERAND,
18721: NULL PRICING_ATTRIBUTE_DATATYPE
18722: FROM
18723: qp_npreq_lines_tmp QPLINES,
18724: qp_npreq_line_attrs_tmp QPLAT_PROD,
18725: QP_PRICING_ATTRIBUTES QPA,
18726: QP_LIST_HEADERS_B QPH,
18727: QP_LIST_LINES QPL,
18728: QP_PRICE_REQ_SOURCES_V QPRS
18729: WHERE QPA.PRICING_PHASE_ID = P_PRICING_PHASE_ID
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,
18836: QPA.PRICING_ATTRIBUTE_DATATYPE PRICING_ATTRIBUTE_DATATYPE
18837: FROM
18838: qp_npreq_lines_tmp QPLINES,
18839: qp_npreq_line_attrs_tmp QPLAT_PROD,
18840: QP_PRICING_ATTRIBUTES QPA,
18841: qp_npreq_line_attrs_tmp QPLAT_PRIC,
18842: QP_LIST_HEADERS_B QPH,
18843: QP_LIST_LINES QPL,
18844: QP_PRICE_REQ_SOURCES QPRS
18912:
18913: CURSOR L_OTHER_LIST_LINE_CUR (L_LINE_INDEX PLS_INTEGER,
18914: L_LIST_LINE_ID NUMBER,
18915: P_FREEZE_OVERRIDE_FLAG VARCHAR2)IS
18916: SELECT /*+ ORDERED USE_NL(QPLAT_PROD QPA QPL QPH ) index(QPA QP_PRICING_ATTRIBUTES_N5) index(qph qp_list_headers_b_n7) l_other_list_line_cur */
18917: QPLINES.LINE_INDEX,
18918: QPL.LIST_LINE_ID,
18919: QPL.ARITHMETIC_OPERATOR,
18920: QPL.OPERAND,
18965: NULL PRICING_ATTRIBUTE_DATATYPE
18966: FROM
18967: qp_npreq_lines_tmp QPLINES,
18968: qp_npreq_line_attrs_tmp QPLAT_PROD,
18969: QP_PRICING_ATTRIBUTES QPA,
18970: QP_LIST_HEADERS_B QPH,
18971: QP_LIST_LINES QPL,
18972: QP_PRICE_REQ_SOURCES QPRS
18973: WHERE QPA.PRICING_PHASE_ID = P_PRICING_PHASE_ID
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,
19080: QPA.PRICING_ATTRIBUTE_DATATYPE PRICING_ATTRIBUTE_DATATYPE
19081: FROM
19082: qp_npreq_lines_tmp QPLINES,
19083: qp_npreq_line_attrs_tmp QPLAT_PROD,
19084: QP_PRICING_ATTRIBUTES QPA,
19085: qp_npreq_line_attrs_tmp QPLAT_PRIC,
19086: QP_LIST_HEADERS_B QPH,
19087: QP_LIST_LINES QPL,
19088: QP_PRICE_REQ_SOURCES QPRS
21647: /*
21648: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,QP_PREQ_QUAL_TMP_N5,PRICING_STATUS_CODE,1
21649: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,QP_PREQ_QUAL_TMP_N5,SEARCH_IND,2
21650:
21651: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,QP_PRICING_ATTRIBUTES_N7,LIST_HEADER_ID,1
21652: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,QP_PRICING_ATTRIBUTES_N7,QUALIFICATION_IND,2
21653: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,QP_PRICING_ATTRIBUTES_N7,PRICING_PHASE_ID,3
21654: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,QP_PRICING_ATTRIBUTES_N7,LIST_LINE_ID,4
21655:
21648: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,QP_PREQ_QUAL_TMP_N5,PRICING_STATUS_CODE,1
21649: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,QP_PREQ_QUAL_TMP_N5,SEARCH_IND,2
21650:
21651: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,QP_PRICING_ATTRIBUTES_N7,LIST_HEADER_ID,1
21652: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,QP_PRICING_ATTRIBUTES_N7,QUALIFICATION_IND,2
21653: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,QP_PRICING_ATTRIBUTES_N7,PRICING_PHASE_ID,3
21654: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,QP_PRICING_ATTRIBUTES_N7,LIST_LINE_ID,4
21655:
21656: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
21649: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,QP_PREQ_QUAL_TMP_N5,SEARCH_IND,2
21650:
21651: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,QP_PRICING_ATTRIBUTES_N7,LIST_HEADER_ID,1
21652: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,QP_PRICING_ATTRIBUTES_N7,QUALIFICATION_IND,2
21653: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,QP_PRICING_ATTRIBUTES_N7,PRICING_PHASE_ID,3
21654: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,QP_PRICING_ATTRIBUTES_N7,LIST_LINE_ID,4
21655:
21656: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
21657: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
21650:
21651: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,QP_PRICING_ATTRIBUTES_N7,LIST_HEADER_ID,1
21652: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,QP_PRICING_ATTRIBUTES_N7,QUALIFICATION_IND,2
21653: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,QP_PRICING_ATTRIBUTES_N7,PRICING_PHASE_ID,3
21654: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,QP_PRICING_ATTRIBUTES_N7,LIST_LINE_ID,4
21655:
21656: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
21657: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
21658: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,qp_npreq_line_attrs_tmp_N2,CONTEXT,3
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,
21719: where pricing_status_code = G_STATUS_UNCHANGED
21720: and pricing_phase_id = p_pricing_phase_id
21721: and search_ind = 1
21722: and list_line_id > 0 ) qpq,
21723: qp_pricing_attributes qpprod,
21724: qp_npreq_line_attrs_tmp qplatpr,
21725: qp_list_lines qpl,
21726: qp_npreq_lines_tmp qplines
21727: where
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,
21809: and pricing_phase_id = p_pricing_phase_id
21810: and search_ind = 1
21811: and list_line_id < 0 ) qpq,
21812: qp_npreq_line_attrs_tmp qplatpr,
21813: qp_pricing_attributes qpprod,
21814: qp_list_lines qpl,
21815: qp_npreq_lines_tmp qplines
21816: where
21817: qpprod.list_header_id = qpq.list_header_id
21844: nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) <> G_YES)
21845: order by 4,2;
21846:
21847: /*
21848: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
21849: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
21850: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
21851: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
21852: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
21845: order by 4,2;
21846:
21847: /*
21848: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
21849: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
21850: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
21851: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
21852: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
21853: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
21846:
21847: /*
21848: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
21849: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
21850: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
21851: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
21852: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
21853: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
21854: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
21847: /*
21848: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
21849: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
21850: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
21851: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
21852: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
21853: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
21854: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
21855: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
21848: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
21849: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
21850: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
21851: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
21852: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
21853: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
21854: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
21855: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
21856:
21849: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
21850: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
21851: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
21852: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
21853: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
21854: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
21855: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
21856:
21857: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PREQ_QUAL_TMP_N5,PRICING_STATUS_CODE,1
21850: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
21851: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
21852: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
21853: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
21854: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
21855: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
21856:
21857: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PREQ_QUAL_TMP_N5,PRICING_STATUS_CODE,1
21858: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PREQ_QUAL_TMP_N5,SEARCH_IND,2
21851: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
21852: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
21853: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
21854: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
21855: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
21856:
21857: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PREQ_QUAL_TMP_N5,PRICING_STATUS_CODE,1
21858: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PREQ_QUAL_TMP_N5,SEARCH_IND,2
21859: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_PREQ_QUAL_TMP_N5,LIST_HEADER_ID,3
21876: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,QP_PREQ_QUAL_TMP_N5,PRICING_STATUS_CODE,1
21877: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,QP_PREQ_QUAL_TMP_N5,SEARCH_IND,2
21878: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,QP_PREQ_QUAL_TMP_N5,LINE_INDEX,5
21879:
21880: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,QP_PRICING_ATTRIBUTES_N7,LIST_HEADER_ID,1
21881: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,QP_PRICING_ATTRIBUTES_N7,QUALIFICATION_IND,2
21882: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,QP_PRICING_ATTRIBUTES_N7,PRICING_PHASE_ID,3
21883: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,QP_PRICING_ATTRIBUTES_N7,LIST_LINE_ID,4
21884:
21877: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,QP_PREQ_QUAL_TMP_N5,SEARCH_IND,2
21878: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,QP_PREQ_QUAL_TMP_N5,LINE_INDEX,5
21879:
21880: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,QP_PRICING_ATTRIBUTES_N7,LIST_HEADER_ID,1
21881: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,QP_PRICING_ATTRIBUTES_N7,QUALIFICATION_IND,2
21882: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,QP_PRICING_ATTRIBUTES_N7,PRICING_PHASE_ID,3
21883: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,QP_PRICING_ATTRIBUTES_N7,LIST_LINE_ID,4
21884:
21885: -- Used for qplatpr and qplatpc
21878: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,QP_PREQ_QUAL_TMP_N5,LINE_INDEX,5
21879:
21880: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,QP_PRICING_ATTRIBUTES_N7,LIST_HEADER_ID,1
21881: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,QP_PRICING_ATTRIBUTES_N7,QUALIFICATION_IND,2
21882: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,QP_PRICING_ATTRIBUTES_N7,PRICING_PHASE_ID,3
21883: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,QP_PRICING_ATTRIBUTES_N7,LIST_LINE_ID,4
21884:
21885: -- Used for qplatpr and qplatpc
21886: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
21879:
21880: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,QP_PRICING_ATTRIBUTES_N7,LIST_HEADER_ID,1
21881: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,QP_PRICING_ATTRIBUTES_N7,QUALIFICATION_IND,2
21882: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,QP_PRICING_ATTRIBUTES_N7,PRICING_PHASE_ID,3
21883: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,QP_PRICING_ATTRIBUTES_N7,LIST_LINE_ID,4
21884:
21885: -- Used for qplatpr and qplatpc
21886: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
21887: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
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,
21961: where pricing_status_code = G_STATUS_UNCHANGED
21962: and pricing_phase_id = p_pricing_phase_id
21963: and search_ind = 1
21964: and list_line_id > 0 ) qpq,
21965: QP_PRICING_ATTRIBUTES qpprod ,
21966: qp_npreq_line_attrs_tmp qplatpr,
21967: qp_npreq_line_attrs_tmp qplatpc,
21968: qp_list_lines qpl ,
21969: qp_npreq_lines_tmp qplines
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,
22075: and pricing_phase_id = p_pricing_phase_id
22076: and search_ind = 1
22077: and list_line_id < 0 ) qpq,
22078: qp_npreq_line_attrs_tmp qplatpr,
22079: QP_PRICING_ATTRIBUTES qpprod ,
22080: qp_npreq_line_attrs_tmp qplatpc,
22081: qp_list_lines qpl ,
22082: qp_npreq_lines_tmp qplines
22083: where
22280: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE,4
22281: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,qp_npreq_line_attrs_tmp_N2,LINE_INDEX,5
22282: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,qp_npreq_line_attrs_tmp_N2,VALUE_FROM,6
22283:
22284: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
22285: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
22286: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
22287: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
22288: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
22281: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,qp_npreq_line_attrs_tmp_N2,LINE_INDEX,5
22282: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,qp_npreq_line_attrs_tmp_N2,VALUE_FROM,6
22283:
22284: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
22285: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
22286: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
22287: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
22288: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
22289: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
22282: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,qp_npreq_line_attrs_tmp_N2,VALUE_FROM,6
22283:
22284: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
22285: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
22286: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
22287: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
22288: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
22289: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
22290:
22283:
22284: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
22285: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
22286: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
22287: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
22288: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
22289: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
22290:
22291: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
22284: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
22285: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
22286: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
22287: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
22288: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
22289: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
22290:
22291: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
22292:
22285: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
22286: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
22287: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
22288: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
22289: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
22290:
22291: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
22292:
22293: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
22297: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICE_REQ_SOURCES_PK,SOURCE_SYSTEM_CODE,2
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,
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,
22349: qpl.accum_attr_run_src_flag -- accum range break
22350: FROM
22351: qp_npreq_lines_tmp QPLINES,
22352: qp_npreq_line_attrs_tmp qplatpr,
22353: QP_PRICING_ATTRIBUTES QPPROD ,
22354: QP_LIST_HEADERS_B QPH ,
22355: QP_LIST_LINES QPL ,
22356: --QP_PRICE_REQ_SOURCES QPRS
22357: QP_PRICE_REQ_SOURCES_V QPRS /* yangli */
22438: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE,4
22439: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,qp_npreq_line_attrs_tmp_N2,LINE_INDEX,5
22440: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,qp_npreq_line_attrs_tmp_N2,VALUE_FROM,6
22441:
22442: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
22443: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
22444: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
22445: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
22446: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
22439: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,qp_npreq_line_attrs_tmp_N2,LINE_INDEX,5
22440: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,qp_npreq_line_attrs_tmp_N2,VALUE_FROM,6
22441:
22442: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
22443: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
22444: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
22445: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
22446: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
22447: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
22440: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,qp_npreq_line_attrs_tmp_N2,VALUE_FROM,6
22441:
22442: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
22443: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
22444: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
22445: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
22446: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
22447: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
22448:
22441:
22442: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
22443: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
22444: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
22445: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
22446: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
22447: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
22448:
22449: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
22442: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
22443: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
22444: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
22445: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
22446: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
22447: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
22448:
22449: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
22450: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
22443: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
22444: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
22445: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
22446: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
22447: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
22448:
22449: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
22450: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
22451: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,qp_npreq_line_attrs_tmp_N2,LINE_INDEX,5
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,
22521: qpl.accum_attr_run_src_flag -- accum range break
22522: FROM
22523: qp_npreq_lines_tmp QPLINES,
22524: qp_npreq_line_attrs_tmp qplatpr,
22525: QP_PRICING_ATTRIBUTES QPPROD ,
22526: qp_npreq_line_attrs_tmp qplatpc,
22527: QP_LIST_HEADERS_B QPH ,
22528: QP_LIST_LINES QPL ,
22529: --QP_PRICE_REQ_SOURCES qprs
22726: nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) <> G_YES)
22727: order by 4,2;
22728:
22729: /*
22730: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
22731: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
22732: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
22733: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
22734: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
22727: order by 4,2;
22728:
22729: /*
22730: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
22731: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
22732: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
22733: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
22734: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
22735: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
22728:
22729: /*
22730: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
22731: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
22732: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
22733: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
22734: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
22735: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
22736:
22729: /*
22730: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
22731: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
22732: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
22733: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
22734: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
22735: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
22736:
22737: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
22730: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
22731: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
22732: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
22733: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
22734: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
22735: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
22736:
22737: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
22738: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
22731: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
22732: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
22733: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
22734: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
22735: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
22736:
22737: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
22738: INDX,QP_PREQ_GRP.select_modifiers.l_get_excluded_prods_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
22739:
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,
22765: -1 qualifier_precedence,
22766: a.line_detail_index
22767: FROM
22768: qp_npreq_line_attrs_tmp c,
22769: qp_pricing_attributes b,
22770: qp_npreq_line_attrs_tmp a
22771: WHERE b.pricing_phase_id = p_pricing_phase_id
22772: AND b.qualification_ind in (G_YES_PROD_IND,G_YES_PROD_HDR_QUAL_IND,G_YES_PROD_LINE_QUAL_IND,G_YES_PROD_HDR_LINE_QUAL_IND)
22773: AND b.product_attribute_context = c.context
25364: INDX,QP_PREQ_GRP.select_modifiers.l_excl_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE,4
25365: INDX,QP_PREQ_GRP.select_modifiers.l_excl_cur,qp_npreq_line_attrs_tmp_N2,LINE_INDEX,5
25366: INDX,QP_PREQ_GRP.select_modifiers.l_excl_cur,qp_npreq_line_attrs_tmp_N2,VALUE_FROM,6
25367:
25368: INDX,QP_PREQ_GRP.select_modifiers.l_excl_cur,QP_PRICING_ATTRIBUTES_N2,LIST_LINE_ID,1
25369: INDX,QP_PREQ_GRP.select_modifiers.l_excl_cur,QP_PRICING_ATTRIBUTES_N2,EXCLUDER_FLAG,2
25370:
25371: INDX,QP_PREQ_GRP.select_modifiers.l_excl_cur,qp_npreq_ldets_tmp_N2,PRICING_PHASE_ID,1
25372: INDX,QP_PREQ_GRP.select_modifiers.l_excl_cur,qp_npreq_ldets_tmp_N2,PRICING_STATUS_CODE,2
25365: INDX,QP_PREQ_GRP.select_modifiers.l_excl_cur,qp_npreq_line_attrs_tmp_N2,LINE_INDEX,5
25366: INDX,QP_PREQ_GRP.select_modifiers.l_excl_cur,qp_npreq_line_attrs_tmp_N2,VALUE_FROM,6
25367:
25368: INDX,QP_PREQ_GRP.select_modifiers.l_excl_cur,QP_PRICING_ATTRIBUTES_N2,LIST_LINE_ID,1
25369: INDX,QP_PREQ_GRP.select_modifiers.l_excl_cur,QP_PRICING_ATTRIBUTES_N2,EXCLUDER_FLAG,2
25370:
25371: INDX,QP_PREQ_GRP.select_modifiers.l_excl_cur,qp_npreq_ldets_tmp_N2,PRICING_PHASE_ID,1
25372: INDX,QP_PREQ_GRP.select_modifiers.l_excl_cur,qp_npreq_ldets_tmp_N2,PRICING_STATUS_CODE,2
25373:
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
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
25386: AND b.excluder_flag = G_YES
25387: AND b.product_attribute_context = c.context
25607: qp_qualifiers qpq,
25608: qp_list_headers_b qph,
25609: qp_price_req_sources_v qprs,
25610: qp_npreq_line_attrs_tmp qplatpr,
25611: qp_pricing_attributes qpprod,
25612: qp_list_lines qpl
25613: WHERE qph.list_header_id = qpl.list_header_id
25614: --added for moac -- commented references to security profile
25615: --security will be built into qp_list_headers_b
25715: qp_qualifiers qpq,
25716: qp_list_headers_b qph,
25717: qp_price_req_sources_v qprs,
25718: qp_npreq_line_attrs_tmp qplatpr,
25719: qp_pricing_attributes qpprod,
25720: qp_list_lines qpl
25721: WHERE qph.list_header_id = qpl.list_header_id
25722: --added for moac -- commented references to security profile
25723: --security will be built into qp_list_headers_b
25823: qp_qualifiers qpq,
25824: qp_list_headers_b qph,
25825: qp_price_req_sources_v qprs,
25826: qp_npreq_line_attrs_tmp qplatpr,
25827: qp_pricing_attributes qpprod,
25828: qp_list_lines qpl
25829: WHERE qph.list_header_id = qpl.list_header_id
25830: --added for moac -- commented references to security profile
25831: --security will be built into qp_list_headers_b
25950: qp_qualifiers qpq,
25951: qp_list_headers_b qph,
25952: qp_price_req_sources_v qprs,
25953: qp_npreq_line_attrs_tmp qplatpr,
25954: qp_pricing_attributes qpprod,
25955: qp_list_lines qpl,
25956: qp_currency_details qcdt
25957: WHERE qph.list_header_id = qpl.list_header_id
25958: --added for moac -- commented references to security profile
26105: qp_qualifiers qpq,
26106: qp_list_headers_b qph,
26107: qp_price_req_sources_v qprs,
26108: qp_npreq_line_attrs_tmp qplatpr,
26109: qp_pricing_attributes qpprod,
26110: qp_list_lines qpl,
26111: qp_currency_details qcdt
26112: WHERE qph.list_header_id = qpl.list_header_id
26113: --added for moac -- commented references to security profile
26260: qp_qualifiers qpq,
26261: qp_list_headers_b qph,
26262: qp_price_req_sources_v qprs,
26263: qp_npreq_line_attrs_tmp qplatpr,
26264: qp_pricing_attributes qpprod,
26265: qp_list_lines qpl,
26266: qp_currency_details qcdt
26267: WHERE qph.list_header_id = qpl.list_header_id
26268: --added for moac -- commented references to security profile
26362: AND ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') ='N' )
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)
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,
26378: qpl.list_line_id,
26430: qp_qualifiers qpq ,
26431: qp_list_headers_b qph,
26432: qp_price_req_sources_v qprs,
26433: qp_npreq_line_attrs_tmp qplatpr,
26434: qp_pricing_attributes qpprod ,
26435: qp_npreq_line_attrs_tmp qplatpc,
26436: qp_list_lines qpl
26437: WHERE qph.list_header_id = qpq.list_header_id
26438: -- 3594459, context and attribute OR'd together
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,
26506: qpl.list_line_id,
26558: qp_qualifiers qpq ,
26559: qp_list_headers_b qph,
26560: qp_price_req_sources_v qprs,
26561: qp_npreq_line_attrs_tmp qplatpr,
26562: qp_pricing_attributes qpprod ,
26563: qp_npreq_line_attrs_tmp qplatpc,
26564: qp_list_lines qpl
26565: WHERE qph.list_header_id = qpq.list_header_id
26566: -- 3594459, context and attribute OR'd together
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,
26634: qpl.list_line_id,
26686: qp_qualifiers qpq ,
26687: qp_list_headers_b qph,
26688: qp_price_req_sources_v qprs,
26689: qp_npreq_line_attrs_tmp qplatpr,
26690: qp_pricing_attributes qpprod ,
26691: qp_npreq_line_attrs_tmp qplatpc,
26692: qp_list_lines qpl
26693: WHERE qph.list_header_id = qpq.list_header_id
26694: -- 3594459, context and attribute OR'd together
26756: AND ((nvl(qplatq.DERIVED_QUALIFIER_FLAG,'N') ='N' )
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
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,
26776: qpl.list_line_id,
26834: qp_qualifiers qpq ,
26835: qp_list_headers_b qph,
26836: qp_price_req_sources_v qprs,
26837: qp_npreq_line_attrs_tmp qplatpr,
26838: qp_pricing_attributes qpprod ,
26839: qp_npreq_line_attrs_tmp qplatpc,
26840: qp_list_lines qpl,
26841: qp_currency_details qcdt
26842: WHERE qph.list_header_id = qpq.list_header_id
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,
26951: qpl.list_line_id,
27009: qp_qualifiers qpq ,
27010: qp_list_headers_b qph,
27011: qp_price_req_sources_v qprs,
27012: qp_npreq_line_attrs_tmp qplatpr,
27013: qp_pricing_attributes qpprod ,
27014: qp_npreq_line_attrs_tmp qplatpc,
27015: qp_list_lines qpl,
27016: qp_currency_details qcdt
27017: WHERE qph.list_header_id = qpq.list_header_id
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,
27126: qpl.list_line_id,
27184: qp_qualifiers qpq ,
27185: qp_list_headers_b qph,
27186: qp_price_req_sources_v qprs,
27187: qp_npreq_line_attrs_tmp qplatpr,
27188: qp_pricing_attributes qpprod ,
27189: qp_npreq_line_attrs_tmp qplatpc,
27190: qp_list_lines qpl,
27191: qp_currency_details qcdt
27192: WHERE qph.list_header_id = qpq.list_header_id
27336: FROM qp_npreq_lines_tmp
27337: WHERE processed_code IN (G_STS_LHS_NOT_FOUND,G_NO_LIST_PASSED)
27338: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
27339: qp_npreq_line_attrs_tmp qplatpr,
27340: qp_pricing_attributes qpprod ,
27341: qp_list_lines qpl ,
27342: qp_list_headers_b qph ,
27343: qp_price_req_sources_v qprs
27344: WHERE
27423: FROM qp_npreq_lines_tmp
27424: WHERE processed_code IN (G_STS_LHS_NOT_FOUND,G_NO_LIST_PASSED)
27425: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
27426: qp_npreq_line_attrs_tmp qplatpr,
27427: qp_pricing_attributes qpprod,
27428: qp_list_lines qpl,
27429: qp_list_headers_b qph,
27430: qp_price_req_sources_v qprs,
27431: qp_currency_details qcdt
27502: AND QPLATPR.LINE_INDEX = QPLINES.LINE_INDEX -- 4331801/4365538
27503: AND (instr(QPLINES.PROCESS_STATUS,'FREEGOOD')=0) -- bug 2693700
27504: ORDER BY 4,2;
27505:
27506: --Added INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5) for 3259932
27507: --[julin/5007789] Tuned query as follows:
27508: -- 1. Performing line filter first.
27509: -- 2. Merged qplatpr subquery conditions into main where clause.
27510: -- 3. Merged qplatpc subquery conditions into main where clause.
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,
27554: FROM qp_npreq_lines_tmp
27555: WHERE processed_code IN (G_STS_LHS_NOT_FOUND,G_NO_LIST_PASSED)
27556: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
27557: qp_npreq_line_attrs_tmp qplatpr,
27558: qp_pricing_attributes qpprod,
27559: qp_npreq_line_attrs_tmp qplatpc,
27560: qp_list_lines qpl,
27561: qp_list_headers_b qph,
27562: qp_price_req_sources_v QPRS
27604: --AND qplatpc.pricing_attr_flag = G_YES --[julin/4708044] currently not reliable value, not used in index
27605: AND (instr(QPLINES.PROCESS_STATUS,'FREEGOOD')=0) -- bug 2693700
27606: ORDER BY 4,2;
27607:
27608: --Added INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5) for 3259932
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
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,
27660: FROM qp_npreq_lines_tmp
27661: WHERE processed_code IN (G_STS_LHS_NOT_FOUND,G_NO_LIST_PASSED)
27662: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
27663: qp_npreq_line_attrs_tmp qplatpr,
27664: qp_pricing_attributes qpprod ,
27665: qp_npreq_line_attrs_tmp qplatpc,
27666: qp_list_lines qpl ,
27667: qp_list_headers_b qph,
27668: qp_price_req_sources_v qprs,
29916: WHERE line_detail_index = l_line_detail_index
29917: AND ATTRIBUTE_LEVEL = G_DETAIL_LEVEL;
29918:
29919: /*
29920: INDX,QP_PREQ_GRP.call_calculation_engine.l_list_line_attribute_cur,QP_PRICING_ATTRIBUTES_N2,LIST_LINE_ID,1
29921: */
29922: CURSOR l_list_line_attribute_cur(p_list_line_id PLS_INTEGER) IS
29923: SELECT /*+ index(qpp qp_pricing_attributes_n2) l_list_line_attribute_cur */ PRICING_ATTRIBUTE
29924: FROM QP_PRICING_ATTRIBUTES qpp
29919: /*
29920: INDX,QP_PREQ_GRP.call_calculation_engine.l_list_line_attribute_cur,QP_PRICING_ATTRIBUTES_N2,LIST_LINE_ID,1
29921: */
29922: CURSOR l_list_line_attribute_cur(p_list_line_id PLS_INTEGER) IS
29923: SELECT /*+ index(qpp qp_pricing_attributes_n2) l_list_line_attribute_cur */ PRICING_ATTRIBUTE
29924: FROM QP_PRICING_ATTRIBUTES qpp
29925: WHERE LIST_LINE_ID = p_list_line_id
29926: AND PRICING_ATTRIBUTE_CONTEXT = G_PRIC_VOLUME_CONTEXT;
29927:
29920: INDX,QP_PREQ_GRP.call_calculation_engine.l_list_line_attribute_cur,QP_PRICING_ATTRIBUTES_N2,LIST_LINE_ID,1
29921: */
29922: CURSOR l_list_line_attribute_cur(p_list_line_id PLS_INTEGER) IS
29923: SELECT /*+ index(qpp qp_pricing_attributes_n2) l_list_line_attribute_cur */ PRICING_ATTRIBUTE
29924: FROM QP_PRICING_ATTRIBUTES qpp
29925: WHERE LIST_LINE_ID = p_list_line_id
29926: AND PRICING_ATTRIBUTE_CONTEXT = G_PRIC_VOLUME_CONTEXT;
29927:
29928: -- Cursor for getting the related line index for the line index