[Home] [Help]
8152: AND qpprod.line_index = qppric.line_index
8153: AND rownum = 1*/
8154: )
8155: AND NOT EXISTS (SELECT 'x' --do not delete header for which product is not defined
8156: FROM qp_list_lines d
8157: WHERE d.list_header_id = a.list_header_id
8158: AND d.qualification_ind in (G_YES_HDR_LINE_QUAL_IND,G_YES_HDR_QUAL_IND)
8159: AND d.pricing_phase_id = p_pricing_phase_id
8160: AND rownum=1);
8639: -- Get the list header id for the list line
8640: BEGIN
8641: SELECT list_header_id
8642: INTO G_LIST_HEADER_ID_TBL(K)
8643: FROM qp_list_lines
8644: WHERE list_line_id = to_number(p_qual_tbl(I).QUALIFIER_ATTR_VALUE_FROM);
8645: EXCEPTION
8646: WHEN NO_DATA_FOUND THEN
8647: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
9509: AND QPLAT_PRICING.ATTRIBUTE_TYPE IN ('PRICING','PRODUCT');
9510: --AND QPLAT_PRICING.PRICING_STATUS_CODE = G_STATUS_NEW;
9511:
9512: /*
9513: INDX,QP_PREQ_GRP.populate_output.l_ldets_cur,QP_LIST_LINES_PK,LIST_LINE_ID,1
9514:
9515: INDX,QP_PREQ_GRP.populate_output.l_ldets_cur,qp_npreq_ldets_tmp_N4,PRICING_STATUS_CODE,1
9516:
9517: INDX,QP_PREQ_GRP.populate_output.l_ldets_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
9582: b.break_uom_code, /* proration */
9583: b.break_uom_context, /* proration */
9584: b.break_uom_attribute /* proration */
9585: FROM qp_npreq_ldets_tmp a,
9586: QP_LIST_LINES b
9587: WHERE a.CREATED_FROM_LIST_LINE_ID = b.LIST_LINE_ID
9588: AND a.PRICING_STATUS_CODE = G_STATUS_NEW;
9589:
9590: /*
9684: AND QPLAT_PRICING.ATTRIBUTE_TYPE IN ('PRICING','PRODUCT');
9685: --AND QPLAT_PRICING.PRICING_STATUS_CODE = G_STATUS_NEW;
9686:
9687: /*
9688: INDX,QP_PREQ_GRP.populate_output.l_int_ldets_cur,QP_LIST_LINES_PK,LIST_LINE_ID,1
9689:
9690: INDX,QP_PREQ_GRP.populate_output.l_int_ldets_cur,qp_int_ldets_N4,PRICING_STATUS_CODE,1
9691:
9692: INDX,QP_PREQ_GRP.populate_output.l_int_ldets_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
9754: b.break_uom_code, /* proration */
9755: b.break_uom_context, /* proration */
9756: b.break_uom_attribute /* proration */
9757: FROM qp_int_ldets a,
9758: QP_LIST_LINES b
9759: WHERE a.CREATED_FROM_LIST_LINE_ID = b.LIST_LINE_ID
9760: AND a.PRICING_STATUS_CODE = G_STATUS_NEW;
9761:
9762: /*
11710:
11711: INDX,QP_PREQ_GRP.handle_break.l_price_break_detail_cur,QP_PRICING_ATTRIBUTES_N4,PRICING_ATTRIBUTE_CONTEXT,1
11712: INDX,QP_PREQ_GRP.handle_break.l_price_break_detail_cur,QP_PRICING_ATTRIBUTES_N4,PRICING_ATTRIBUTE,2
11713:
11714: INDX,QP_PREQ_GRP.handle_break.l_price_break_detail_cur,QP_LIST_LINES_PK,LIST_LINE_ID,1
11715: */
11716:
11717: cursor l_price_break_detail_multi_cur(p_reduced_pbh_processing VARCHAR2) is
11718: SELECT /*+ ordered use_nl(qplines, qprm, qppa, qpll, qph, qcdt) */
11769: qp_npreq_line_attrs_tmp qplat_pricing,
11770: qp_npreq_lines_tmp qplines,
11771: qp_rltd_modifiers qprm,
11772: qp_pricing_attributes qppa,
11773: qp_list_lines qpll
11774: ,qp_list_headers_b qph /* Vivek */
11775: ,qp_currency_details qcdt /* Vivek */
11776: WHERE qprm.from_rltd_modifier_id = p_list_line_id
11777: AND qprm.to_rltd_modifier_id = qppa.list_line_id
11902: qp_npreq_line_attrs_tmp qplat_pricing,
11903: qp_npreq_lines_tmp qplines,
11904: qp_rltd_modifiers qprm,
11905: qp_pricing_attributes qppa,
11906: qp_list_lines qpll
11907: WHERE qprm.from_rltd_modifier_id = p_list_line_id
11908: AND qprm.to_rltd_modifier_id = qppa.list_line_id
11909: AND qppa.list_line_id = qpll.list_line_id
11910: AND qplat_pricing.context = G_PRIC_VOLUME_CONTEXT
11934: ORDER BY qp_number.canonical_to_number(qppa.pricing_attr_value_from); --bug#1819397
11935:
11936: CURSOR l_pbh_cur IS
11937: SELECT net_amount_flag
11938: FROM qp_list_lines
11939: WHERE list_line_id = p_list_line_id;
11940:
11941: J PLS_INTEGER:=1;
11942: N PLS_INTEGER:=1;
14870: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
14871: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
14872: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
14873:
14874: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_LIST_LINES_PK,LIST_LINE_ID,1
14875:
14876: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
14877:
14878: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
14943: qp_npreq_lines_tmp QPLINES,
14944: QP_QUALIFIERS QPQ,
14945: qp_npreq_line_attrs_tmp QPLAT_PROD,
14946: QP_PRICING_ATTRIBUTES QPA,
14947: QP_LIST_LINES QPL,
14948: QP_LIST_HEADERS_B QPH,
14949: --QP_PRICE_REQ_SOURCES QPRS
14950: QP_PRICE_REQ_SOURCES_V QPRS /*yangli*/
14951: WHERE QPQ.QUALIFIER_CONTEXT = G_LIST_HEADER_CONTEXT
15065: QP_QUALIFIERS qpq,
15066: qp_npreq_line_attrs_tmp qplat_prod,
15067: QP_PRICING_ATTRIBUTES qpa,
15068: qp_npreq_line_attrs_tmp qplat_pric,
15069: QP_LIST_LINES qpl,
15070: QP_LIST_HEADERS_B qph,
15071: --QP_PRICE_REQ_SOURCES qprs
15072: QP_PRICE_REQ_SOURCES_V qprs /* yangli */
15073: WHERE QPQ.QUALIFIER_CONTEXT = G_LIST_HEADER_CONTEXT
15193: QP_QUALIFIERS QPSQ,
15194: qp_npreq_line_attrs_tmp QPLAT_PROD,
15195: qp_npreq_line_attrs_tmp QPLAT_Q,
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*/
15201: WHERE QPQ.QUALIFIER_CONTEXT = G_LIST_HEADER_CONTEXT
15335: qp_npreq_line_attrs_tmp qplat_prod,
15336: QP_PRICING_ATTRIBUTES qpa,
15337: qp_npreq_line_attrs_tmp qplat_pric,
15338: qp_npreq_line_attrs_tmp qplat_q,
15339: QP_LIST_LINES qpl,
15340: QP_LIST_HEADERS_B qph,
15341: --QP_PRICE_REQ_SOURCES qprs
15342: QP_PRICE_REQ_SOURCES_V qprs /* yangli */
15343: WHERE QPQ.QUALIFIER_CONTEXT = G_LIST_HEADER_CONTEXT
15490: qp_npreq_lines_tmp QPLINES,
15491: QP_QUALIFIERS QPQ,
15492: qp_npreq_line_attrs_tmp QPLAT_PROD,
15493: QP_PRICING_ATTRIBUTES QPA,
15494: QP_LIST_LINES QPL,
15495: QP_LIST_HEADERS_B QPH,
15496: --QP_PRICE_REQ_SOURCES QPRS,
15497: QP_PRICE_REQ_SOURCES_V QPRS, /* yangli */
15498: QP_CURRENCY_DETAILS QCDT /* Vivek */
15672: QP_QUALIFIERS qpq,
15673: qp_npreq_line_attrs_tmp qplat_prod,
15674: QP_PRICING_ATTRIBUTES qpa,
15675: qp_npreq_line_attrs_tmp qplat_pric,
15676: QP_LIST_LINES qpl,
15677: QP_LIST_HEADERS_B qph,
15678: --QP_PRICE_REQ_SOURCES qprs,
15679: QP_PRICE_REQ_SOURCES_V qprs, /* yangli */
15680: QP_CURRENCY_DETAILS QCDT /* Vivek */
15860: QP_QUALIFIERS QPSQ,
15861: qp_npreq_line_attrs_tmp QPLAT_PROD,
15862: qp_npreq_line_attrs_tmp QPLAT_Q,
15863: QP_PRICING_ATTRIBUTES QPA,
15864: QP_LIST_LINES QPL,
15865: QP_LIST_HEADERS_B QPH,
15866: --QP_PRICE_REQ_SOURCES QPRS,
15867: QP_PRICE_REQ_SOURCES_V QPRS, /* yangli */
15868: QP_CURRENCY_DETAILS QCDT /* Vivek */
16062: qp_npreq_line_attrs_tmp qplat_prod,
16063: QP_PRICING_ATTRIBUTES qpa,
16064: qp_npreq_line_attrs_tmp qplat_pric,
16065: qp_npreq_line_attrs_tmp qplat_q,
16066: QP_LIST_LINES qpl,
16067: QP_LIST_HEADERS_B qph,
16068: --QP_PRICE_REQ_SOURCES qprs,
16069: QP_PRICE_REQ_SOURCES_V qprs, /* yangli */
16070: QP_CURRENCY_DETAILS QCDT /* Vivek */
17110: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
17111: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
17112: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
17113:
17114: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_LIST_LINES_PK,LIST_LINE_ID,1
17115:
17116: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
17117:
17118: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
17161: FROM
17162: qp_npreq_lines_tmp QPLINES,
17163: qp_npreq_line_attrs_tmp qplat_prod,
17164: QP_PRICING_ATTRIBUTES QPA,
17165: QP_LIST_LINES QPL,
17166: QP_LIST_HEADERS_B QPH,
17167: --QP_PRICE_REQ_SOURCES qprs
17168: QP_PRICE_REQ_SOURCES_V qprs /* yangli */
17169: WHERE QPA.PRICING_PHASE_ID = G_PRICE_LIST_PHASE_ID
17267: qp_npreq_lines_tmp qplines,
17268: qp_npreq_line_attrs_tmp qplat_prod,
17269: QP_PRICING_ATTRIBUTES qpa,
17270: qp_npreq_line_attrs_tmp qplat_pric,
17271: QP_LIST_LINES qpl,
17272: QP_LIST_HEADERS_B qph,
17273: --QP_PRICE_REQ_SOURCES qprs
17274: QP_PRICE_REQ_SOURCES_V qprs /* yangli */
17275: WHERE QPA.PRICING_PHASE_ID = G_PRICE_LIST_PHASE_ID
17392: FROM
17393: qp_npreq_lines_tmp QPLINES,
17394: qp_npreq_line_attrs_tmp qplat_prod,
17395: QP_PRICING_ATTRIBUTES QPA,
17396: QP_LIST_LINES QPL,
17397: QP_LIST_HEADERS_B QPH,
17398: --QP_PRICE_REQ_SOURCES qprs,
17399: QP_PRICE_REQ_SOURCES_V qprs, /* yangli */
17400: QP_CURRENCY_DETAILS QCDT /* Vivek */
17550: qp_npreq_lines_tmp qplines,
17551: qp_npreq_line_attrs_tmp qplat_prod,
17552: QP_PRICING_ATTRIBUTES qpa,
17553: qp_npreq_line_attrs_tmp qplat_pric,
17554: QP_LIST_LINES qpl,
17555: QP_LIST_HEADERS_B qph,
17556: --QP_PRICE_REQ_SOURCES qprs,
17557: QP_PRICE_REQ_SOURCES_V qprs, /* yangli */
17558: QP_CURRENCY_DETAILS QCDT /* Vivek */
17693: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_line_attrs_tmp_N1,LINE_INDEX,1
17694: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_line_attrs_tmp_N1,ATTRIBUTE_TYPE,2
17695: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_line_attrs_tmp_N1,PRICING_STATUS_CODE,3
17696:
17697: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_LIST_LINES_PK,LIST_LINE_ID,1
17698:
17699: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
17700:
17701: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
17767: QP_QUALIFIERS QPQ,
17768: qp_npreq_line_attrs_tmp QPLAT_Q,
17769: qp_npreq_line_attrs_tmp QPLAT_PROD,
17770: QP_PRICING_ATTRIBUTES QPA,
17771: QP_LIST_LINES QPL,
17772: QP_LIST_HEADERS_B QPH,
17773: --QP_PRICE_REQ_SOURCES QPRS
17774: QP_PRICE_REQ_SOURCES_V QPRS /* yangli */
17775: WHERE QPA.PRICING_PHASE_ID = G_PRICE_LIST_PHASE_ID
17895: qp_npreq_line_attrs_tmp QPLAT_Q,
17896: qp_npreq_line_attrs_tmp QPLAT_PROD,
17897: QP_PRICING_ATTRIBUTES QPA,
17898: qp_npreq_line_attrs_tmp QPLAT_PRIC,
17899: QP_LIST_LINES QPL,
17900: QP_LIST_HEADERS_B QPH,
17901: --QP_PRICE_REQ_SOURCES QPRS
17902: QP_PRICE_REQ_SOURCES_V QPRS /* yangli */
17903: WHERE QPA.PRICING_PHASE_ID = G_PRICE_LIST_PHASE_ID
18028: FROM
18029: qp_npreq_lines_tmp QPLINES,
18030: qp_npreq_line_attrs_tmp qplat_prod,
18031: QP_PRICING_ATTRIBUTES QPA,
18032: QP_LIST_LINES QPL,
18033: QP_LIST_HEADERS_B QPH,
18034: --QP_PRICE_REQ_SOURCES qprs
18035: QP_PRICE_REQ_SOURCES_V qprs /* yangli */
18036: WHERE QPA.PRICING_PHASE_ID = G_PRICE_LIST_PHASE_ID
18131: qp_npreq_lines_tmp qplines,
18132: qp_npreq_line_attrs_tmp qplat_prod,
18133: QP_PRICING_ATTRIBUTES qpa,
18134: qp_npreq_line_attrs_tmp qplat_pric,
18135: QP_LIST_LINES qpl,
18136: QP_LIST_HEADERS_B qph,
18137: --QP_PRICE_REQ_SOURCES qprs
18138: QP_PRICE_REQ_SOURCES_V qprs /* yangli */
18139: WHERE QPA.PRICING_PHASE_ID = G_PRICE_LIST_PHASE_ID
18253: QP_QUALIFIERS QPQ,
18254: qp_npreq_line_attrs_tmp QPLAT_Q,
18255: qp_npreq_line_attrs_tmp QPLAT_PROD,
18256: QP_PRICING_ATTRIBUTES QPA,
18257: QP_LIST_LINES QPL,
18258: QP_LIST_HEADERS_B QPH,
18259: --QP_PRICE_REQ_SOURCES QPRS,
18260: QP_PRICE_REQ_SOURCES_V QPRS, /* yangli */
18261: QP_CURRENCY_DETAILS QCDT /* Vivek */
18439: qp_npreq_line_attrs_tmp QPLAT_Q,
18440: qp_npreq_line_attrs_tmp QPLAT_PROD,
18441: QP_PRICING_ATTRIBUTES QPA,
18442: qp_npreq_line_attrs_tmp QPLAT_PRIC,
18443: QP_LIST_LINES QPL,
18444: QP_LIST_HEADERS_B QPH,
18445: --QP_PRICE_REQ_SOURCES QPRS,
18446: QP_PRICE_REQ_SOURCES_V QPRS, /* yangli */
18447: QP_CURRENCY_DETAILS QCDT /* Vivek */
18630: FROM
18631: qp_npreq_lines_tmp QPLINES,
18632: qp_npreq_line_attrs_tmp qplat_prod,
18633: QP_PRICING_ATTRIBUTES QPA,
18634: QP_LIST_LINES QPL,
18635: QP_LIST_HEADERS_B QPH,
18636: --QP_PRICE_REQ_SOURCES qprs,
18637: QP_PRICE_REQ_SOURCES_V qprs, /* yangli */
18638: QP_CURRENCY_DETAILS QCDT /* Vivek */
18794: qp_npreq_lines_tmp qplines,
18795: qp_npreq_line_attrs_tmp qplat_prod,
18796: QP_PRICING_ATTRIBUTES qpa,
18797: qp_npreq_line_attrs_tmp qplat_pric,
18798: QP_LIST_LINES qpl,
18799: QP_LIST_HEADERS_B qph,
18800: --QP_PRICE_REQ_SOURCES qprs,
18801: QP_PRICE_REQ_SOURCES_V qprs, /* yangli */
18802: QP_CURRENCY_DETAILS QCDT /* Vivek */
18966:
18967: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
18968: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICE_REQ_SOURCES_PK,SOURCE_SYSTEM_CODE,2
18969:
18970: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_LIST_LINES_PK,LIST_LINE_ID,1
18971:
18972: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N4,PRICING_ATTRIBUTE_CONTEXT,1
18973: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N4,PRICING_ATTRIBUTE,2
18974: */
19032: qp_npreq_lines_tmp QPLINES,
19033: qp_npreq_line_attrs_tmp QPLAT_PROD,
19034: QP_PRICING_ATTRIBUTES QPA,
19035: QP_LIST_HEADERS_B QPH,
19036: QP_LIST_LINES QPL,
19037: QP_PRICE_REQ_SOURCES_V QPRS
19038: WHERE QPA.PRICING_PHASE_ID = P_PRICING_PHASE_ID
19039: AND QPA.QUALIFICATION_IND IN (QP_PREQ_GRP.G_YES_PROD_IND, QP_PREQ_GRP.G_YES_PROD_HDR_QUAL_IND,
19040: QP_PREQ_GRP.G_YES_PROD_LINE_QUAL_IND,QP_PREQ_GRP.G_YES_PROD_HDR_LINE_QUAL_IND)
19150: qp_npreq_line_attrs_tmp QPLAT_PROD,
19151: QP_PRICING_ATTRIBUTES QPA,
19152: qp_npreq_line_attrs_tmp QPLAT_PRIC,
19153: QP_LIST_HEADERS_B QPH,
19154: QP_LIST_LINES QPL,
19155: QP_PRICE_REQ_SOURCES QPRS
19156: WHERE QPA.PRICING_PHASE_ID = p_pricing_phase_id
19157: AND QPA.QUALIFICATION_IND IN (QP_PREQ_GRP.G_YES_PROD_PRIC_IND, QP_PREQ_GRP.G_YES_PROD_PRIC_HDR_QUAL_IND,
19158: QP_PREQ_GRP.G_YES_PROD_PRIC_LINE_QUAL_IND,QP_PREQ_GRP.G_YES_PRIC_HDR_LINE_QUAL_IND)
19280: qp_npreq_lines_tmp QPLINES,
19281: qp_npreq_line_attrs_tmp QPLAT_PROD,
19282: QP_PRICING_ATTRIBUTES QPA,
19283: QP_LIST_HEADERS_B QPH,
19284: QP_LIST_LINES QPL,
19285: QP_PRICE_REQ_SOURCES QPRS
19286: WHERE QPA.PRICING_PHASE_ID = P_PRICING_PHASE_ID
19287: AND QPA.QUALIFICATION_IND IN (QP_PREQ_GRP.G_YES_PROD_IND, QP_PREQ_GRP.G_YES_PROD_HDR_QUAL_IND,
19288: QP_PREQ_GRP.G_YES_PROD_LINE_QUAL_IND,QP_PREQ_GRP.G_YES_PROD_HDR_LINE_QUAL_IND)
19398: qp_npreq_line_attrs_tmp QPLAT_PROD,
19399: QP_PRICING_ATTRIBUTES QPA,
19400: qp_npreq_line_attrs_tmp QPLAT_PRIC,
19401: QP_LIST_HEADERS_B QPH,
19402: QP_LIST_LINES QPL,
19403: QP_PRICE_REQ_SOURCES QPRS
19404: WHERE QPA.PRICING_PHASE_ID = p_pricing_phase_id
19405: AND QPA.QUALIFICATION_IND IN (QP_PREQ_GRP.G_YES_PROD_PRIC_IND, QP_PREQ_GRP.G_YES_PROD_PRIC_HDR_QUAL_IND,
19406: QP_PREQ_GRP.G_YES_PROD_PRIC_LINE_QUAL_IND,QP_PREQ_GRP.G_YES_PRIC_HDR_LINE_QUAL_IND)
21149: ORDER BY 1,2;
21150:
21151: CURSOR L_SEC_LST_LIN_P1_QLFY_SEC_Y IS
21152: SELECT
21153: /*+ ORDERED index(qpl QP_LIST_LINES_N16)
21154: index(qplines QP_PREQ_LINES_TMP_U1)
21155: index(qppattmp QP_NPREQ_LINE_PATRNS_T_N2)
21156: index(qppqual QP_PREQ_PATRN_QUAL_T_N2)
21157: L_SEC_LST_LIN_P1_QLFY_SEC_Y */
21185: qpl.PRICE_BREAK_TYPE_CODE
21186: FROM qp_npreq_lines_tmp qplines,
21187: qp_npreq_line_patrns_tmp qppattmp,
21188: qp_preq_patrn_qual_tmp qppqual,
21189: qp_list_lines qpl
21190: WHERE qppattmp.LINE_INDEX = qplines.LINE_INDEX
21191: AND qppqual.LINE_INDEX = qplines.LINE_INDEX
21192: AND QPL.PRICING_PHASE_ID = G_PRICE_LIST_PHASE_ID
21193: AND qpl.pattern_id = qppattmp.pattern_id
21348: ORDER BY 1,2;
21349:
21350: CURSOR L_SEC_LST_LIN_P1_QLFY_Y_CURR IS
21351: SELECT
21352: /*+ ORDERED index(qpl QP_LIST_LINES_N16)
21353: index(qplines QP_PREQ_LINES_TMP_U1)
21354: index(qppattmp QP_NPREQ_LINE_PATRNS_T_N2)
21355: index(qppqual QP_PREQ_PATRN_QUAL_T_N2)
21356: L_SEC_LST_LIN_P1_QLFY_Y_CURR */
21388: qpl.PRICE_BREAK_TYPE_CODE
21389: FROM qp_npreq_lines_tmp qplines,
21390: qp_npreq_line_patrns_tmp qppattmp,
21391: qp_preq_patrn_qual_tmp qppqual,
21392: qp_list_lines qpl
21393: WHERE qppattmp.LINE_INDEX = qplines.LINE_INDEX
21394: AND qppqual.LINE_INDEX = qplines.LINE_INDEX
21395: AND QPL.PRICING_PHASE_ID = G_PRICE_LIST_PHASE_ID
21396: AND qpl.pattern_id = qppattmp.pattern_id
21533: ORDER BY 1,2;
21534:
21535: CURSOR L_SEC_LST_LIN_P1_QLFY_N_CURR IS
21536: SELECT
21537: /*+ ORDERED index(qpl QP_LIST_LINES_N16)
21538: index(qplines QP_PREQ_LINES_TMP_U1)
21539: index(qppattmp QP_NPREQ_LINE_PATRNS_T_N2)
21540: index(qppqual QP_PREQ_PATRN_QUAL_T_N2)
21541: L_SEC_LST_LIN_P1_QLFY_N_CURR */
21573: qpl.PRICE_BREAK_TYPE_CODE
21574: FROM qp_npreq_lines_tmp qplines,
21575: qp_npreq_line_patrns_tmp qppattmp,
21576: qp_preq_patrn_qual_tmp qppqual,
21577: qp_list_lines qpl
21578: WHERE qppattmp.LINE_INDEX = qplines.LINE_INDEX
21579: AND qppqual.LINE_INDEX = qplines.LINE_INDEX
21580: AND QPL.PRICING_PHASE_ID = G_PRICE_LIST_PHASE_ID
21581: AND qpl.pattern_id = qppattmp.pattern_id
21664: ORDER BY 1,2;
21665:
21666: CURSOR L_SEC_LST_LIN_P1_QLFY_SEC_N IS
21667: SELECT
21668: /*+ ORDERED index(qpl QP_LIST_LINES_N16)
21669: index(qplines QP_PREQ_LINES_TMP_U1)
21670: index(qppattmp QP_NPREQ_LINE_PATRNS_T_N2)
21671: index(qppqual QP_PREQ_PATRN_QUAL_T_N2)
21672: L_SEC_LST_LIN_P1_QLFY_SEC_N */
21700: qpl.PRICE_BREAK_TYPE_CODE
21701: FROM qp_npreq_lines_tmp qplines,
21702: qp_npreq_line_patrns_tmp qppattmp,
21703: qp_preq_patrn_qual_tmp qppqual,
21704: qp_list_lines qpl
21705: WHERE qppattmp.LINE_INDEX = qplines.LINE_INDEX
21706: AND qppqual.LINE_INDEX = qplines.LINE_INDEX
21707: AND QPL.PRICING_PHASE_ID = G_PRICE_LIST_PHASE_ID
21708: AND qpl.pattern_id = qppattmp.pattern_id
21727: CURSOR L_SEC_SRCH_BOTH_P2_QLFY_N IS
21728:
21729: SELECT /*+ ORDERED index(qplines QP_PREQ_LINES_TMP_U1)
21730: index(QPQ QP_QUALIFIERS_N4)
21731: index(qpl QP_LIST_LINES_N17)
21732: index(qppattmp1 QP_NPREQ_LINE_PATRNS_T_N2)
21733: index(qpatrgrp QP_ATTRIBUTE_GROUPS_N1)
21734: index(qprs qp_pte_source_systems_u2)
21735: L_SEC_SRCH_BOTH_P2_QLFY_N */
21775: qpl.PRICE_BREAK_TYPE_CODE
21776: FROM qp_npreq_lines_tmp qplines,
21777: QP_QUALIFIERS QPQ,
21778: qp_npreq_line_patrns_tmp qppattmp1,
21779: qp_list_lines qpl,
21780: qp_secu_attr_groups_v qpatrgrp,
21781: --qp_attribute_groups qpatrgrp,
21782: qp_pte_source_systems qprs
21783: --qp_price_req_sources_v qprs
21831: CURSOR L_SEC_SRCH_BOTH_P2_QLFY_N_CURR IS
21832:
21833: SELECT /*+ ORDERED index(qplines QP_PREQ_LINES_TMP_U1)
21834: index(QPQ QP_QUALIFIERS_N4)
21835: index(qpl QP_LIST_LINES_N16)
21836: index(qppattmp1 QP_NPREQ_LINE_PATRNS_T_N2)
21837: index(qpatrgrp QP_ATTRIBUTE_GROUPS_N1)
21838: index(qcdt QP_CURRENCY_DETAILS_N2)
21839: index(qprs qp_pte_source_systems_u2)
21880: qpl.PRICE_BREAK_TYPE_CODE
21881: FROM qp_npreq_lines_tmp qplines,
21882: QP_QUALIFIERS QPQ,
21883: qp_npreq_line_patrns_tmp qppattmp1,
21884: qp_list_lines qpl,
21885: qp_secu_attr_groups_v qpatrgrp,
21886: --qp_attribute_groups qpatrgrp,
21887: qp_pte_source_systems qprs,
21888: --qp_price_req_sources_v qprs,
23544:
23545: CURSOR l_pat_cache_both IS
23546:
23547: SELECT /*+ ORDERED index(qplines QP_PREQ_LINES_TMP_N3)
23548: index(qpl QP_LIST_LINES_N16)
23549: index(qppattmp1 QP_NPREQ_LINE_PATRNS_T_N2)
23550: index(qpatrgrp QP_ATTRIBUTE_GROUPS_N1)
23551: index(qppattmp2 QP_NPREQ_LINE_PATRNS_T_N1)
23552: index(qprs qp_pte_source_systems_u2)
23592: qpatrgrp.descendents_quals_exist,
23593: QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
23594: FROM qp_npreq_lines_tmp qplines,
23595: qp_npreq_line_patrns_tmp qppattmp1,
23596: qp_list_lines qpl,
23597: qp_secu_attr_groups_v qpatrgrp,
23598: --qp_attribute_groups qpatrgrp,
23599: qp_pte_source_systems qprs,
23600: --qp_price_req_sources_v qprs,
23631: AND qpatrgrp.CURRENCY_CODE = QPLINES.CURRENCY_CODE
23632: AND (instr(QPLINES.PROCESS_STATUS,'FREEGOOD')=0)
23633: UNION ALL
23634: SELECT /*+ ORDERED index(qplines QP_PREQ_LINES_TMP_N3)
23635: index(qpl QP_LIST_LINES_N16)
23636: index(qppattmp1 QP_NPREQ_LINE_PATRNS_T_N2)
23637: index(qprs qp_pte_source_systems_u2)
23638: l_pat_cache_both */
23639: DISTINCT
23674: 'N' descendents_quals_exist,
23675: QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
23676: FROM qp_npreq_lines_tmp qplines,
23677: qp_npreq_line_patrns_tmp qppattmp1,
23678: qp_list_lines qpl,
23679: qp_list_headers_b qph,
23680: qp_pte_source_systems qprs
23681: --qp_price_req_sources_v qprs
23682: WHERE QPLINES.VALIDATED_FLAG = G_YES
23707: CURSOR l_pat_cache_both_curr IS
23708:
23709: SELECT /*+ ORDERED index(QCDT QP_CURRENCY_DETAILS_N2)
23710: index(qplines QP_PREQ_LINES_TMP_N3)
23711: index(qpl QP_LIST_LINES_N16)
23712: index(qppattmp1 QP_NPREQ_LINE_PATRNS_T_N2)
23713: index(qpatrgrp QP_ATTRIBUTE_GROUPS_N1)
23714: index(qppattmp2 QP_NPREQ_LINE_PATRNS_T_N1)
23715: index(qprs qp_pte_source_systems_u2)
23753: qpatrgrp.descendents_quals_exist,
23754: QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
23755: FROM qp_npreq_lines_tmp qplines,
23756: qp_npreq_line_patrns_tmp qppattmp1,
23757: qp_list_lines qpl,
23758: qp_secu_attr_groups_v qpatrgrp,
23759: --qp_attribute_groups qpatrgrp,
23760: qp_pte_source_systems qprs,
23761: --qp_price_req_sources_v qprs,
23846: AND (instr(QPLINES.PROCESS_STATUS,'FREEGOOD')=0)
23847: UNION
23848: SELECT /*+ ORDERED index(QCDT QP_CURRENCY_DETAILS_N2)
23849: index(qplines QP_PREQ_LINES_TMP_N3)
23850: index(qpl QP_LIST_LINES_N16)
23851: index(qppattmp1 QP_NPREQ_LINE_PATRNS_T_N2)
23852: index(qprs qp_pte_source_systems_u2)
23853: l_pat_cache_both_curr */
23854: DISTINCT
23890: 'N' descendents_quals_exist,
23891: QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
23892: FROM qp_npreq_lines_tmp qplines,
23893: qp_npreq_line_patrns_tmp qppattmp1,
23894: qp_list_lines qpl,
23895: qp_list_headers_b qph,
23896: --qp_price_req_sources_v qprs,
23897: qp_pte_source_systems qprs,
23898: QP_CURRENCY_DETAILS QCDT
25480: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE,4
25481: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,qp_npreq_line_attrs_tmp_N2,LINE_INDEX,5
25482: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,qp_npreq_line_attrs_tmp_N2,VALUE_FROM,6
25483:
25484: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,QP_LIST_LINES_PK,LIST_LINE_ID,1
25485:
25486: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
25487: */
25488:
25544: and search_ind = 1
25545: and list_line_id > 0 ) qpq,
25546: qp_pricing_attributes qpprod,
25547: qp_npreq_line_attrs_tmp qplatpr,
25548: qp_list_lines qpl,
25549: qp_npreq_lines_tmp qplines
25550: where
25551: qpprod.list_header_id = qpq.list_header_id
25552: and qpprod.list_line_id = qpq.list_line_id
25635: and search_ind = 1
25636: and list_line_id < 0 ) qpq,
25637: qp_npreq_line_attrs_tmp qplatpr,
25638: qp_pricing_attributes qpprod,
25639: qp_list_lines qpl,
25640: qp_npreq_lines_tmp qplines
25641: where
25642: qpprod.list_header_id = qpq.list_header_id
25643: and qpl.list_line_id = qpprod.list_line_id
25692: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE,4
25693: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,qp_npreq_line_attrs_tmp_N2,LINE_INDEX,5
25694: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,qp_npreq_line_attrs_tmp_N2,VALUE_FROM,6
25695:
25696: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_LIST_LINES_PK,LIST_LINE_ID,1
25697:
25698: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,qp_npreq_lines_tmp_U1,LINE_INDEX,1
25699: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,qp_npreq_lines_tmp_U1,LINE_TYPE_CODE,2
25700:
25714: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE,4
25715: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,qp_npreq_line_attrs_tmp_N2,LINE_INDEX,5
25716: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,qp_npreq_line_attrs_tmp_N2,VALUE_FROM,6
25717:
25718: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,QP_LIST_LINES_PK,LIST_LINE_ID,1
25719:
25720: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,qp_npreq_lines_tmp_U1,LINE_INDEX,1
25721: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,qp_npreq_lines_tmp_U1,LINE_TYPE_CODE,2
25722: */
25791: and list_line_id > 0 ) qpq,
25792: QP_PRICING_ATTRIBUTES qpprod ,
25793: qp_npreq_line_attrs_tmp qplatpr,
25794: qp_npreq_line_attrs_tmp qplatpc,
25795: qp_list_lines qpl ,
25796: qp_npreq_lines_tmp qplines
25797: where
25798: qpprod.list_header_id = qpq.list_header_id
25799: and qpprod.list_line_id = qpq.list_line_id
25906: and list_line_id < 0 ) qpq,
25907: qp_npreq_line_attrs_tmp qplatpr,
25908: QP_PRICING_ATTRIBUTES qpprod ,
25909: qp_npreq_line_attrs_tmp qplatpc,
25910: qp_list_lines qpl ,
25911: qp_npreq_lines_tmp qplines
25912: where
25913: qpprod.list_header_id = qpq.list_header_id
25914: and qpl.list_line_id = qpprod.list_line_id
25955: INDX,QP_PREQ_GRP.select_modifiers.l_inner_qual_cur,QP_PREQ_QUAL_TMP_N5,SEARCH_IND,2
25956: INDX,QP_PREQ_GRP.select_modifiers.l_inner_qual_cur,QP_PREQ_QUAL_TMP_N5,list_header_id,3
25957: INDX,QP_PREQ_GRP.select_modifiers.l_inner_qual_cur,QP_PREQ_QUAL_TMP_N5,list_line_id,4
25958:
25959: INDX,QP_PREQ_GRP.select_modifiers.l_inner_qual_cur,QP_LIST_LINES_N5,PRICING_PHASE_ID,1
25960: INDX,QP_PREQ_GRP.select_modifiers.l_inner_qual_cur,QP_LIST_LINES_N5,QUALIFICATION_IND,2
25961: */
25962: CURSOR l_INNER_QUAL_CUR(p_pricing_phase_id NUMBER,
25963: p_list_header_id NUMBER,
25956: INDX,QP_PREQ_GRP.select_modifiers.l_inner_qual_cur,QP_PREQ_QUAL_TMP_N5,list_header_id,3
25957: INDX,QP_PREQ_GRP.select_modifiers.l_inner_qual_cur,QP_PREQ_QUAL_TMP_N5,list_line_id,4
25958:
25959: INDX,QP_PREQ_GRP.select_modifiers.l_inner_qual_cur,QP_LIST_LINES_N5,PRICING_PHASE_ID,1
25960: INDX,QP_PREQ_GRP.select_modifiers.l_inner_qual_cur,QP_LIST_LINES_N5,QUALIFICATION_IND,2
25961: */
25962: CURSOR l_INNER_QUAL_CUR(p_pricing_phase_id NUMBER,
25963: p_list_header_id NUMBER,
25964: p_list_line_id NUMBER,
25967: p_start_date_active_first DATE,
25968: p_start_date_active_second DATE,
25969: p_line_type_code VARCHAR2) IS
25970: -- Qualifier Only
25971: -- 5092968, qp_list_lines_n6 replaced with qp_list_lines_pk
25972: select /*+ ORDERED INDEX(qpl,QP_LIST_LINES_PK) l_inner_qual_cur */
25973: qpl.list_header_id,
25974: qpl.list_line_id,
25975: qpl.list_line_type_code,
25968: p_start_date_active_second DATE,
25969: p_line_type_code VARCHAR2) IS
25970: -- Qualifier Only
25971: -- 5092968, qp_list_lines_n6 replaced with qp_list_lines_pk
25972: select /*+ ORDERED INDEX(qpl,QP_LIST_LINES_PK) l_inner_qual_cur */
25973: qpl.list_header_id,
25974: qpl.list_line_id,
25975: qpl.list_line_type_code,
25976: qpq.line_index,
26013: where pricing_status_code = G_STATUS_UNCHANGED
26014: and pricing_phase_id = p_pricing_phase_id
26015: and search_ind = 1
26016: and list_line_id > 0 ) qpq,
26017: qp_list_lines qpl
26018: where
26019: qpl.list_header_id = qpq.list_header_id
26020: and qpl.list_line_id = qpq.list_line_id
26021: and (qpl.modifier_level_code = qpq.line_type_code
26036: G_COUPON_ISSUE,G_TERMS_SUBSTITUTION,G_ITEM_UPGRADE)
26037: or
26038: nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) <> G_YES)
26039: union all
26040: select /*+ ORDERED INDEX(qpl,QP_LIST_LINES_N5) l_inner_qual_cur */
26041: qpl.list_header_id,
26042: qpl.list_line_id,
26043: qpl.list_line_type_code,
26044: qpq.line_index,
26081: where pricing_status_code = G_STATUS_UNCHANGED
26082: and pricing_phase_id = p_pricing_phase_id
26083: and search_ind = 1
26084: and list_line_id < 0 ) qpq,
26085: qp_list_lines qpl
26086: where
26087: qpl.list_header_id = qpq.list_header_id
26088: and (qpl.modifier_level_code = qpq.line_type_code
26089: OR
26186: qp_npreq_lines_tmp QPLINES,
26187: qp_npreq_line_attrs_tmp qplatpr,
26188: QP_PRICING_ATTRIBUTES QPPROD ,
26189: QP_LIST_HEADERS_B QPH ,
26190: QP_LIST_LINES QPL ,
26191: --QP_PRICE_REQ_SOURCES QPRS
26192: QP_PRICE_REQ_SOURCES_V QPRS /* yangli */
26193: WHERE
26194: (qplines.price_flag = G_YES
26361: qp_npreq_line_attrs_tmp qplatpr,
26362: QP_PRICING_ATTRIBUTES QPPROD ,
26363: qp_npreq_line_attrs_tmp qplatpc,
26364: QP_LIST_HEADERS_B QPH ,
26365: QP_LIST_LINES QPL ,
26366: --QP_PRICE_REQ_SOURCES qprs
26367: QP_PRICE_REQ_SOURCES_V qprs /* yangli */
26368: WHERE
26369: (qplines.price_flag = G_YES
26448: nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) <> G_YES)
26449: order by 4,2;
26450:
26451: /*
26452: INDX,QP_PREQ_GRP.select_modifiers.l_cur,QP_LIST_LINES_N5,PRICING_PHASE_ID,1
26453: INDX,QP_PREQ_GRP.select_modifiers.l_cur,QP_LIST_LINES_N5,QUALIFICATION_IND,2
26454:
26455: INDX,QP_PREQ_GRP.select_modifiers.l_cur,QP_LIST_HEADERS_B_N2,LIST_HEADER_ID,1
26456: INDX,QP_PREQ_GRP.select_modifiers.l_cur,QP_LIST_HEADERS_B_N2,ACTIVE_FLAG,2
26449: order by 4,2;
26450:
26451: /*
26452: INDX,QP_PREQ_GRP.select_modifiers.l_cur,QP_LIST_LINES_N5,PRICING_PHASE_ID,1
26453: INDX,QP_PREQ_GRP.select_modifiers.l_cur,QP_LIST_LINES_N5,QUALIFICATION_IND,2
26454:
26455: INDX,QP_PREQ_GRP.select_modifiers.l_cur,QP_LIST_HEADERS_B_N2,LIST_HEADER_ID,1
26456: INDX,QP_PREQ_GRP.select_modifiers.l_cur,QP_LIST_HEADERS_B_N2,ACTIVE_FLAG,2
26457: INDX,QP_PREQ_GRP.select_modifiers.l_cur,QP_LIST_HEADERS_B_N2,CURRENCY_CODE,3
26460: INDX,QP_PREQ_GRP.select_modifiers.l_cur,QP_PRICE_REQ_SOURCES_PK,SOURCE_SYSTEM_CODE,2
26461: */
26462: cursor l_CUR (p_pricing_phase_id NUMBER) IS
26463: -- NO PRODUCTS AND NO QUALIFIERS
26464: SELECT /*+ NO_EXPAND ORDERED USE_NL(QPL QPH QPRS) index(QPL QP_LIST_LINES_N5) index(qph qp_list_headers_b_n7) l_cur_mod */
26465: qpl.list_header_id,
26466: qpl.list_line_id,
26467: qpl.list_line_type_code,
26468: qplines.line_index,
26497: qpl.accum_attribute, -- accum range break
26498: qpl.accum_attr_run_src_flag -- accum range break
26499: from
26500: qp_npreq_lines_tmp qplines,
26501: qp_list_lines qpl,
26502: qp_list_headers_b qph,
26503: --qp_price_req_sources qprs
26504: qp_price_req_sources_v qprs /* yangli */
26505: where qph.list_header_id = qpl.list_header_id
29507: -- select all list lines matching for product/pricing attributes for the selected headers in the first step
29508:
29509: CURSOR l_pat_line_cache_p1 IS
29510: -- check for all list lines for which header is already selected.
29511: SELECT /*+ ORDERED index(qpl QP_LIST_LINES_N16)
29512: index(qppat QP_NPREQ_LINE_PATRNS_T_N2)
29513: index(qppqual QP_PREQ_PATRN_QUAL_T_N2)
29514: l_pat_line_cache_p1 */
29515: DISTINCT qpl.LIST_HEADER_ID,
29544: 'N' descendents_quals_exist
29545: FROM qp_npreq_lines_tmp qplines,
29546: qp_npreq_line_patrns_tmp qppat,
29547: qp_preq_patrn_qual_tmp qppqual,
29548: qp_list_lines qpl
29549: WHERE qplines.processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
29550: AND nvl(qplines.validated_flag,G_NO) = G_NO
29551: AND qppat.line_index = qplines.line_index
29552: AND qppqual.line_index = qppat.line_index
29569:
29570: CURSOR l_pat_line_p2 IS
29571:
29572: SELECT /*+ ORDERED index(qplines QP_PREQ_LINES_TMP_U1)
29573: index(qpl QP_LIST_LINES_N17)
29574: index(qppattmp1 QP_NPREQ_LINE_PATRNS_T_N2)
29575: index(qpatrgrp QP_ATTRIBUTE_GROUPS_N1)
29576: index(qppattmp2 QP_NPREQ_LINE_PATRNS_T_N1)
29577: index(qprs qp_pte_source_systems_u2)
29616: qpatrgrp.descendents_quals_exist,
29617: qpatrgrp.effective_precedence --bug 12731268
29618: FROM qp_npreq_lines_tmp qplines,
29619: qp_npreq_line_patrns_tmp qppattmp1,
29620: qp_list_lines qpl,
29621: qp_secu_attr_groups_v qpatrgrp,
29622: --qp_attribute_groups qpatrgrp,
29623: qp_pte_source_systems qprs,
29624: --qp_price_req_sources_v qprs,
29664: CURSOR l_pat_line_curr_p2 IS
29665:
29666: SELECT /*+ ORDERED index(QCDT QP_CURRENCY_DETAILS_N2)
29667: index(qplines QP_PREQ_LINES_TMP_U1)
29668: index(qpl QP_LIST_LINES_N17)
29669: index(qppattmp1 QP_NPREQ_LINE_PATRNS_T_N2)
29670: index(qpatrgrp QP_ATTRIBUTE_GROUPS_N1)
29671: index(qppattmp2 QP_NPREQ_LINE_PATRNS_T_N1)
29672: index(qprs qp_pte_source_systems_u2)
29714: qpatrgrp.descendents_quals_exist,
29715: qpatrgrp.effective_precedence --bug 12731268
29716: FROM qp_npreq_lines_tmp qplines,
29717: qp_npreq_line_patrns_tmp qppattmp1,
29718: qp_list_lines qpl,
29719: qp_secu_attr_groups_v qpatrgrp,
29720: --qp_attribute_groups qpatrgrp,
29721: qp_pte_source_systems qprs,
29722: --qp_price_req_sources_v qprs,
29928: -- select all list lines matching for product/pricing attributes for the selected headers in the first step
29929:
29930: CURSOR l_pat_line_cache_curr_p1 IS
29931: -- check for all list lines for which header is already selected.
29932: SELECT /*+ ORDERED index(qpl QP_LIST_LINES_N16)
29933: index(qppat QP_NPREQ_LINE_PATRNS_T_N2)
29934: index(qppqual QP_PREQ_PATRN_QUAL_T_N2)
29935: l_pat_line_cache_curr_p1 */
29936: DISTINCT qpl.LIST_HEADER_ID,
29965: 'N' descendents_quals_exist
29966: FROM qp_npreq_lines_tmp qplines,
29967: qp_npreq_line_patrns_tmp qppat,
29968: qp_preq_patrn_qual_tmp qppqual,
29969: qp_list_lines qpl
29970: WHERE qplines.processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
29971: AND nvl(qplines.validated_flag,G_NO) = G_NO
29972: AND qppat.line_index = qplines.line_index
29973: AND qppqual.line_index = qppat.line_index
31096: --[julin/5007789] Tuned query as follows:
31097: -- 1. Performing line filter first.
31098: -- 2. Performing qualifier filter before product filter
31099: -- 3. Split into unions ('=', 'BETWEEN', 'NOT =') for better use of indexes
31100: -- bug 9210308 added index hint for index QP_LIST_LINES_PK
31101: CURSOR l_prod_qual_cur (p_pricing_phase_id NUMBER) IS
31102: -- Product and Qualifier Only. No pricing
31103: SELECT /*+ ORDERED USE_NL(QPLATQ QPQ QPH QPRS QPLATPR QPPROD QPL)
31104: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
31104: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
31105: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
31106: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
31107: INDEX(QPQ QP_QUALIFIERS_N4)
31108: INDEX(QPL QP_LIST_LINES_PK)
31109: l_prod_qual_cur_pl */
31110: qpl.list_header_id,
31111: qpl.list_line_id,
31112: qpl.list_line_type_code,
31155: qp_list_headers_b qph,
31156: qp_price_req_sources_v qprs,
31157: qp_npreq_line_attrs_tmp qplatpr,
31158: qp_pricing_attributes qpprod,
31159: qp_list_lines qpl
31160: WHERE qph.list_header_id = qpl.list_header_id
31161: --added for moac -- commented references to security profile
31162: --security will be built into qp_list_headers_b
31163: --AND ((nvl(G_SECURITY_CONTROL, 'OFF') = 'ON' AND G_CURRENT_USER_OP_UNIT = QPH.orig_org_id) OR -- begin security
31214: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
31215: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
31216: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
31217: INDEX(QPQ QP_QUALIFIERS_N5)
31218: INDEX(QPL QP_LIST_LINES_PK)
31219: l_prod_qual_cur_pl */
31220: qpl.list_header_id,
31221: qpl.list_line_id,
31222: qpl.list_line_type_code,
31265: qp_list_headers_b qph,
31266: qp_price_req_sources_v qprs,
31267: qp_npreq_line_attrs_tmp qplatpr,
31268: qp_pricing_attributes qpprod,
31269: qp_list_lines qpl
31270: WHERE qph.list_header_id = qpl.list_header_id
31271: --added for moac -- commented references to security profile
31272: --security will be built into qp_list_headers_b
31273: --AND ((nvl(G_SECURITY_CONTROL, 'OFF') = 'ON' AND G_CURRENT_USER_OP_UNIT = QPH.orig_org_id) OR -- begin security
31324: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
31325: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
31326: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
31327: INDEX(QPQ QP_QUALIFIERS_N6)
31328: INDEX(QPL QP_LIST_LINES_PK)
31329: l_prod_qual_cur_pl */
31330: qpl.list_header_id,
31331: qpl.list_line_id,
31332: qpl.list_line_type_code,
31375: qp_list_headers_b qph,
31376: qp_price_req_sources_v qprs,
31377: qp_npreq_line_attrs_tmp qplatpr,
31378: qp_pricing_attributes qpprod,
31379: qp_list_lines qpl
31380: WHERE qph.list_header_id = qpl.list_header_id
31381: --added for moac -- commented references to security profile
31382: --security will be built into qp_list_headers_b
31383: --AND ((nvl(G_SECURITY_CONTROL, 'OFF') = 'ON' AND G_CURRENT_USER_OP_UNIT = QPH.orig_org_id) OR -- begin security
31447: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
31448: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
31449: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
31450: INDEX(QPQ QP_QUALIFIERS_N4)
31451: INDEX(QPL QP_LIST_LINES_PK)
31452: l_prod_qual_cur_pl */
31453: qpl.list_header_id,
31454: qpl.list_line_id,
31455: qpl.list_line_type_code,
31504: qp_list_headers_b qph,
31505: qp_price_req_sources_v qprs,
31506: qp_npreq_line_attrs_tmp qplatpr,
31507: qp_pricing_attributes qpprod,
31508: qp_list_lines qpl,
31509: qp_currency_details qcdt
31510: WHERE qph.list_header_id = qpl.list_header_id
31511: --added for moac -- commented references to security profile
31512: --security will be built into qp_list_headers_b
31604: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
31605: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
31606: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
31607: INDEX(QPQ QP_QUALIFIERS_N5)
31608: INDEX(QPL QP_LIST_LINES_PK)
31609: l_prod_qual_cur_pl */
31610: qpl.list_header_id,
31611: qpl.list_line_id,
31612: qpl.list_line_type_code,
31661: qp_list_headers_b qph,
31662: qp_price_req_sources_v qprs,
31663: qp_npreq_line_attrs_tmp qplatpr,
31664: qp_pricing_attributes qpprod,
31665: qp_list_lines qpl,
31666: qp_currency_details qcdt
31667: WHERE qph.list_header_id = qpl.list_header_id
31668: --added for moac -- commented references to security profile
31669: --security will be built into qp_list_headers_b
31761: INDEX(QPLATQ QP_PREQ_LINE_ATTRS_TMP_N1)
31762: INDEX(QPLAPR QP_PREQ_LINE_ATTRS_TMP_N1)
31763: INDEX(QPPROD QP_PRICING_ATTRIBUTES_N5)
31764: INDEX(QPQ QP_QUALIFIERS_N6)
31765: INDEX(QPL QP_LIST_LINES_PK)
31766: l_prod_qual_cur_pl */
31767: qpl.list_header_id,
31768: qpl.list_line_id,
31769: qpl.list_line_type_code,
31818: qp_list_headers_b qph,
31819: qp_price_req_sources_v qprs,
31820: qp_npreq_line_attrs_tmp qplatpr,
31821: qp_pricing_attributes qpprod,
31822: qp_list_lines qpl,
31823: qp_currency_details qcdt
31824: WHERE qph.list_header_id = qpl.list_header_id
31825: --added for moac -- commented references to security profile
31826: --security will be built into qp_list_headers_b
31989: qp_price_req_sources_v qprs,
31990: qp_npreq_line_attrs_tmp qplatpr,
31991: qp_pricing_attributes qpprod ,
31992: qp_npreq_line_attrs_tmp qplatpc,
31993: qp_list_lines qpl
31994: WHERE qph.list_header_id = qpq.list_header_id
31995: -- 3594459, context and attribute OR'd together
31996: AND (qpq.qualifier_context <> G_LIST_HEADER_CONTEXT --bug 2799670
31997: OR qpq.qualifier_attribute <> G_PRICELIST_ATTRIBUTE) --bug 2799670
32117: qp_price_req_sources_v qprs,
32118: qp_npreq_line_attrs_tmp qplatpr,
32119: qp_pricing_attributes qpprod ,
32120: qp_npreq_line_attrs_tmp qplatpc,
32121: qp_list_lines qpl
32122: WHERE qph.list_header_id = qpq.list_header_id
32123: -- 3594459, context and attribute OR'd together
32124: AND (qpq.qualifier_context <> G_LIST_HEADER_CONTEXT --bug 2799670
32125: OR qpq.qualifier_attribute <> G_PRICELIST_ATTRIBUTE) --bug 2799670
32245: qp_price_req_sources_v qprs,
32246: qp_npreq_line_attrs_tmp qplatpr,
32247: qp_pricing_attributes qpprod ,
32248: qp_npreq_line_attrs_tmp qplatpc,
32249: qp_list_lines qpl
32250: WHERE qph.list_header_id = qpq.list_header_id
32251: -- 3594459, context and attribute OR'd together
32252: AND (qpq.qualifier_context <> G_LIST_HEADER_CONTEXT --bug 2799670
32253: OR qpq.qualifier_attribute <> G_PRICELIST_ATTRIBUTE) --bug 2799670
32393: qp_price_req_sources_v qprs,
32394: qp_npreq_line_attrs_tmp qplatpr,
32395: qp_pricing_attributes qpprod ,
32396: qp_npreq_line_attrs_tmp qplatpc,
32397: qp_list_lines qpl,
32398: qp_currency_details qcdt
32399: WHERE qph.list_header_id = qpq.list_header_id
32400: -- 3594459, context and attribute OR'd together
32401: AND (qpq.qualifier_context <> G_LIST_HEADER_CONTEXT --bug 2799670
32568: qp_price_req_sources_v qprs,
32569: qp_npreq_line_attrs_tmp qplatpr,
32570: qp_pricing_attributes qpprod ,
32571: qp_npreq_line_attrs_tmp qplatpc,
32572: qp_list_lines qpl,
32573: qp_currency_details qcdt
32574: WHERE qph.list_header_id = qpq.list_header_id
32575: -- 3594459, context and attribute OR'd together
32576: AND (qpq.qualifier_context <> G_LIST_HEADER_CONTEXT --bug 2799670
32743: qp_price_req_sources_v qprs,
32744: qp_npreq_line_attrs_tmp qplatpr,
32745: qp_pricing_attributes qpprod ,
32746: qp_npreq_line_attrs_tmp qplatpc,
32747: qp_list_lines qpl,
32748: qp_currency_details qcdt
32749: WHERE qph.list_header_id = qpq.list_header_id
32750: -- 3594459, context and attribute OR'd together
32751: AND (qpq.qualifier_context <> G_LIST_HEADER_CONTEXT --bug 2799670
32857: -- 1. Performing line filter first.
32858: -- 2. Merged qplatpr subquery conditions into main where clause.
32859: CURSOR l_prod_cur (p_pricing_phase_id NUMBER) IS
32860: -- Only Product attributes
32861: -- bug 9210308 - Added index hint for index QP_LIST_LINES_PK
32862: SELECT /*+ ORDERED USE_NL(QPLATPR QPPROD QPL QPH QPRS) index(qplatpr QP_PREQ_LINE_ATTRS_TMP_N1) index(QPL QP_LIST_LINES_PK) l_prod_cur_pl */
32863: qpl.list_header_id,
32864: qpl.list_line_id,
32865: qpl.list_line_type_code,
32858: -- 2. Merged qplatpr subquery conditions into main where clause.
32859: CURSOR l_prod_cur (p_pricing_phase_id NUMBER) IS
32860: -- Only Product attributes
32861: -- bug 9210308 - Added index hint for index QP_LIST_LINES_PK
32862: SELECT /*+ ORDERED USE_NL(QPLATPR QPPROD QPL QPH QPRS) index(qplatpr QP_PREQ_LINE_ATTRS_TMP_N1) index(QPL QP_LIST_LINES_PK) l_prod_cur_pl */
32863: qpl.list_header_id,
32864: qpl.list_line_id,
32865: qpl.list_line_type_code,
32866: qplatpr.line_index,
32895: WHERE processed_code IN (G_STS_LHS_NOT_FOUND,G_NO_LIST_PASSED)
32896: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
32897: qp_npreq_line_attrs_tmp qplatpr,
32898: qp_pricing_attributes qpprod ,
32899: qp_list_lines qpl ,
32900: qp_list_headers_b qph ,
32901: qp_price_req_sources_v qprs
32902: WHERE
32903: qph.list_header_id = qpprod.list_header_id
32939: --[julin/5007789] Tuned query as follows:
32940: -- 1. Performing line filter first.
32941: CURSOR l_prod_cur_currency (p_pricing_phase_id NUMBER) IS
32942: -- Product Only.
32943: SELECT /*+ ORDERED USE_NL(QPLATPR QPPROD QPL QPH QPRS) index(qplatpr QP_PREQ_LINE_ATTRS_TMP_N1) INDEX(QPL QP_LIST_LINES_PK) l_prod_cur_pl */
32944: qpl.list_header_id,
32945: qpl.list_line_id,
32946: qpl.list_line_type_code,
32947: qplatpr.line_index,
32982: WHERE processed_code IN (G_STS_LHS_NOT_FOUND,G_NO_LIST_PASSED)
32983: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
32984: qp_npreq_line_attrs_tmp qplatpr,
32985: qp_pricing_attributes qpprod,
32986: qp_list_lines qpl,
32987: qp_list_headers_b qph,
32988: qp_price_req_sources_v qprs,
32989: qp_currency_details qcdt
32990: WHERE qph.list_header_id = qpl.list_header_id
33117: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
33118: qp_npreq_line_attrs_tmp qplatpr,
33119: qp_pricing_attributes qpprod,
33120: qp_npreq_line_attrs_tmp qplatpc,
33121: qp_list_lines qpl,
33122: qp_list_headers_b qph,
33123: qp_price_req_sources_v QPRS
33124: WHERE
33125: qph.list_header_id = qpl.list_header_id
33223: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
33224: qp_npreq_line_attrs_tmp qplatpr,
33225: qp_pricing_attributes qpprod ,
33226: qp_npreq_line_attrs_tmp qplatpc,
33227: qp_list_lines qpl ,
33228: qp_list_headers_b qph,
33229: qp_price_req_sources_v qprs,
33230: qp_currency_details qcdt
33231: WHERE
39603: AND list_line_id <> -1
39604: AND pricing_status_code = G_STATUS_UNCHANGED
39605: AND line_index BETWEEN l_start_line_index AND l_end_line_index
39606: )qpatq,
39607: QP_LIST_LINES qplines
39608: WHERE qplines.list_line_id = qpatq.list_line_id;
39609:
39610: l_start_line_index := l_end_line_index + 1;
39611: l_end_line_index := l_end_line_index + G_CHUNK_SIZE;
40986: AND pricing_status_code <> G_STATUS_NEW;
40987:
40988: --check if there is such a list line with passed in phase id
40989: /*
40990: INDX,QP_PREQ_GRP.price_request.l_chk_phase_exists,QP_LIST_LINES_N5,PRICING_PHASE_ID,1
40991: */
40992:
40993: -- [julin/4742368/4766135] modified to try list_header_phases first; if not found, go to
40994: -- lines/headers; assuming that phase/qual on lines is a better EXISTS filter than
41005: and qplh.active_flag = 'Y')
41006: OR
41007: EXISTS (
41008: select /*+ ORDERED */ 'x'
41009: from qp_list_lines qpll, qp_list_headers_b qplh
41010: where qpll.pricing_phase_id = p_phase_id
41011: and qpll.qualification_ind in (0, 4, 20)
41012: and qpll.list_header_id = qplh.list_header_id
41013: and qplh.active_flag = 'Y');
41011: and qpll.qualification_ind in (0, 4, 20)
41012: and qpll.list_header_id = qplh.list_header_id
41013: and qplh.active_flag = 'Y');
41014: /*
41015: -- julin [3773033]: removing hint /*+ ordered use_nl(qpll) index(qpll QP_LIST_LINES_N5) /
41016: -- bug 3804392 - exclude factor lists as well
41017: CURSOR l_chk_phase_exists(p_phase_id IN PLS_INTEGER) IS
41018: select 'x'
41019: from qp_list_headers_b qplh, qp_list_lines qpll
41015: -- julin [3773033]: removing hint /*+ ordered use_nl(qpll) index(qpll QP_LIST_LINES_N5) /
41016: -- bug 3804392 - exclude factor lists as well
41017: CURSOR l_chk_phase_exists(p_phase_id IN PLS_INTEGER) IS
41018: select 'x'
41019: from qp_list_headers_b qplh, qp_list_lines qpll
41020: where qplh.active_flag = 'Y'
41021: and qplh.list_type_code not in ('PRL', 'AGR', 'PML')
41022: and qpll.list_header_id = qplh.list_header_id
41023: and qpll.pricing_phase_id = p_phase_id
41023: and qpll.pricing_phase_id = p_phase_id
41024: and qpll.qualification_ind in (0, 2, 4, 6, 8, 10, 12, 14, 20, 22, 28, 30)
41025: and rownum = 1;
41026: select 'X' from dual
41027: where exists (select 'X' from qp_list_lines
41028: where pricing_phase_id = p_phase_id
41029: AND modifier_level_code IN ('LINE','LINEGROUP','ORDER') --3169430
41030: and rownum = 1);*/
41031: