[Home] [Help]
7893: AND qpprod.line_index = qppric.line_index
7894: AND rownum = 1*/
7895: )
7896: AND NOT EXISTS (SELECT 'x' --do not delete header for which product is not defined
7897: FROM qp_list_lines d
7898: WHERE d.list_header_id = a.list_header_id
7899: AND d.qualification_ind in (G_YES_HDR_LINE_QUAL_IND,G_YES_HDR_QUAL_IND)
7900: AND d.pricing_phase_id = p_pricing_phase_id
7901: AND rownum=1);
8374: -- Get the list header id for the list line
8375: BEGIN
8376: SELECT list_header_id
8377: INTO G_LIST_HEADER_ID_TBL(K)
8378: FROM qp_list_lines
8379: WHERE list_line_id = to_number(p_qual_tbl(I).QUALIFIER_ATTR_VALUE_FROM);
8380: EXCEPTION
8381: WHEN NO_DATA_FOUND THEN
8382: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
9239: AND QPLAT_PRICING.ATTRIBUTE_TYPE IN ('PRICING','PRODUCT');
9240: --AND QPLAT_PRICING.PRICING_STATUS_CODE = G_STATUS_NEW;
9241:
9242: /*
9243: INDX,QP_PREQ_GRP.populate_output.l_ldets_cur,QP_LIST_LINES_PK,LIST_LINE_ID,1
9244:
9245: INDX,QP_PREQ_GRP.populate_output.l_ldets_cur,qp_npreq_ldets_tmp_N4,PRICING_STATUS_CODE,1
9246:
9247: INDX,QP_PREQ_GRP.populate_output.l_ldets_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
9310: b.break_uom_code, /* proration */
9311: b.break_uom_context, /* proration */
9312: b.break_uom_attribute /* proration */
9313: FROM qp_npreq_ldets_tmp a,
9314: QP_LIST_LINES b
9315: WHERE a.CREATED_FROM_LIST_LINE_ID = b.LIST_LINE_ID
9316: AND a.PRICING_STATUS_CODE = G_STATUS_NEW;
9317:
9318: /*
9412: AND QPLAT_PRICING.ATTRIBUTE_TYPE IN ('PRICING','PRODUCT');
9413: --AND QPLAT_PRICING.PRICING_STATUS_CODE = G_STATUS_NEW;
9414:
9415: /*
9416: INDX,QP_PREQ_GRP.populate_output.l_int_ldets_cur,QP_LIST_LINES_PK,LIST_LINE_ID,1
9417:
9418: INDX,QP_PREQ_GRP.populate_output.l_int_ldets_cur,qp_int_ldets_N4,PRICING_STATUS_CODE,1
9419:
9420: INDX,QP_PREQ_GRP.populate_output.l_int_ldets_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
9482: b.break_uom_code, /* proration */
9483: b.break_uom_context, /* proration */
9484: b.break_uom_attribute /* proration */
9485: FROM qp_int_ldets a,
9486: QP_LIST_LINES b
9487: WHERE a.CREATED_FROM_LIST_LINE_ID = b.LIST_LINE_ID
9488: AND a.PRICING_STATUS_CODE = G_STATUS_NEW;
9489:
9490: /*
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:
11437: cursor l_price_break_detail_multi_cur(p_reduced_pbh_processing VARCHAR2) is
11438: SELECT /*+ ordered use_nl(qplines, qprm, qppa, qpll, qph, qcdt) */
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
11495: AND qprm.to_rltd_modifier_id = qppa.list_line_id
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
11626: AND qplat_pricing.context = G_PRIC_VOLUME_CONTEXT
11650: ORDER BY qp_number.canonical_to_number(qppa.pricing_attr_value_from); --bug#1819397
11651:
11652: CURSOR l_pbh_cur IS
11653: SELECT net_amount_flag
11654: FROM qp_list_lines
11655: WHERE list_line_id = p_list_line_id;
11656:
11657: J PLS_INTEGER:=1;
11658: N PLS_INTEGER:=1;
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
14580:
14581: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
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*/
14654: WHERE QPQ.QUALIFIER_CONTEXT = G_LIST_HEADER_CONTEXT
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
14775: QP_PRICE_REQ_SOURCES_V qprs /* yangli */
14776: WHERE QPQ.QUALIFIER_CONTEXT = G_LIST_HEADER_CONTEXT
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*/
14904: WHERE QPQ.QUALIFIER_CONTEXT = G_LIST_HEADER_CONTEXT
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,
15044: --QP_PRICE_REQ_SOURCES qprs
15045: QP_PRICE_REQ_SOURCES_V qprs /* yangli */
15046: WHERE QPQ.QUALIFIER_CONTEXT = G_LIST_HEADER_CONTEXT
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 */
15201: QP_CURRENCY_DETAILS QCDT /* Vivek */
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,
15382: QP_PRICE_REQ_SOURCES_V qprs, /* yangli */
15383: QP_CURRENCY_DETAILS QCDT /* Vivek */
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 */
15571: QP_CURRENCY_DETAILS QCDT /* Vivek */
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,
15771: --QP_PRICE_REQ_SOURCES qprs,
15772: QP_PRICE_REQ_SOURCES_V qprs, /* yangli */
15773: QP_CURRENCY_DETAILS QCDT /* Vivek */
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
16811:
16812: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
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 */
16863: WHERE QPA.PRICING_PHASE_ID = G_PRICE_LIST_PHASE_ID
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
16968: QP_PRICE_REQ_SOURCES_V qprs /* yangli */
16969: WHERE QPA.PRICING_PHASE_ID = G_PRICE_LIST_PHASE_ID
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 */
17093: QP_CURRENCY_DETAILS QCDT /* Vivek */
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,
17250: QP_PRICE_REQ_SOURCES_V qprs, /* yangli */
17251: QP_CURRENCY_DETAILS QCDT /* Vivek */
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
17389:
17390: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_LIST_LINES_PK,LIST_LINE_ID,1
17391:
17392: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
17393:
17394: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
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 */
17468: WHERE QPA.PRICING_PHASE_ID = G_PRICE_LIST_PHASE_ID
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
17595: QP_PRICE_REQ_SOURCES_V QPRS /* yangli */
17596: WHERE QPA.PRICING_PHASE_ID = G_PRICE_LIST_PHASE_ID
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 */
17729: WHERE QPA.PRICING_PHASE_ID = G_PRICE_LIST_PHASE_ID
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
17831: QP_PRICE_REQ_SOURCES_V qprs /* yangli */
17832: WHERE QPA.PRICING_PHASE_ID = G_PRICE_LIST_PHASE_ID
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 */
17954: QP_CURRENCY_DETAILS QCDT /* Vivek */
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,
18139: QP_PRICE_REQ_SOURCES_V QPRS, /* yangli */
18140: QP_CURRENCY_DETAILS QCDT /* Vivek */
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 */
18331: QP_CURRENCY_DETAILS QCDT /* Vivek */
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,
18494: QP_PRICE_REQ_SOURCES_V qprs, /* yangli */
18495: QP_CURRENCY_DETAILS QCDT /* Vivek */
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:
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: */
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
18730: AND QPA.QUALIFICATION_IND IN (QP_PREQ_GRP.G_YES_PROD_IND, QP_PREQ_GRP.G_YES_PROD_HDR_QUAL_IND,
18731: QP_PREQ_GRP.G_YES_PROD_LINE_QUAL_IND,QP_PREQ_GRP.G_YES_PROD_HDR_LINE_QUAL_IND)
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
18845: WHERE QPA.PRICING_PHASE_ID = p_pricing_phase_id
18846: AND QPA.QUALIFICATION_IND IN (QP_PREQ_GRP.G_YES_PROD_PRIC_IND, QP_PREQ_GRP.G_YES_PROD_PRIC_HDR_QUAL_IND,
18847: QP_PREQ_GRP.G_YES_PROD_PRIC_LINE_QUAL_IND,QP_PREQ_GRP.G_YES_PRIC_HDR_LINE_QUAL_IND)
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
18974: AND QPA.QUALIFICATION_IND IN (QP_PREQ_GRP.G_YES_PROD_IND, QP_PREQ_GRP.G_YES_PROD_HDR_QUAL_IND,
18975: QP_PREQ_GRP.G_YES_PROD_LINE_QUAL_IND,QP_PREQ_GRP.G_YES_PROD_HDR_LINE_QUAL_IND)
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
19089: WHERE QPA.PRICING_PHASE_ID = p_pricing_phase_id
19090: AND QPA.QUALIFICATION_IND IN (QP_PREQ_GRP.G_YES_PROD_PRIC_IND, QP_PREQ_GRP.G_YES_PROD_PRIC_HDR_QUAL_IND,
19091: QP_PREQ_GRP.G_YES_PROD_PRIC_LINE_QUAL_IND,QP_PREQ_GRP.G_YES_PRIC_HDR_LINE_QUAL_IND)
21659: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE,4
21660: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,qp_npreq_line_attrs_tmp_N2,LINE_INDEX,5
21661: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,qp_npreq_line_attrs_tmp_N2,VALUE_FROM,6
21662:
21663: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,QP_LIST_LINES_PK,LIST_LINE_ID,1
21664:
21665: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
21666: */
21667:
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
21728: qpprod.list_header_id = qpq.list_header_id
21729: and qpprod.list_line_id = qpq.list_line_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
21818: and qpl.list_line_id = qpprod.list_line_id
21867: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE,4
21868: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,qp_npreq_line_attrs_tmp_N2,LINE_INDEX,5
21869: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,qp_npreq_line_attrs_tmp_N2,VALUE_FROM,6
21870:
21871: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_LIST_LINES_PK,LIST_LINE_ID,1
21872:
21873: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,qp_npreq_lines_tmp_U1,LINE_INDEX,1
21874: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,qp_npreq_lines_tmp_U1,LINE_TYPE_CODE,2
21875:
21889: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE,4
21890: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,qp_npreq_line_attrs_tmp_N2,LINE_INDEX,5
21891: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,qp_npreq_line_attrs_tmp_N2,VALUE_FROM,6
21892:
21893: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,QP_LIST_LINES_PK,LIST_LINE_ID,1
21894:
21895: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,qp_npreq_lines_tmp_U1,LINE_INDEX,1
21896: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,qp_npreq_lines_tmp_U1,LINE_TYPE_CODE,2
21897: */
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
21970: where
21971: qpprod.list_header_id = qpq.list_header_id
21972: and qpprod.list_line_id = qpq.list_line_id
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
22084: qpprod.list_header_id = qpq.list_header_id
22085: and qpl.list_line_id = qpprod.list_line_id
22126: INDX,QP_PREQ_GRP.select_modifiers.l_inner_qual_cur,QP_PREQ_QUAL_TMP_N5,SEARCH_IND,2
22127: INDX,QP_PREQ_GRP.select_modifiers.l_inner_qual_cur,QP_PREQ_QUAL_TMP_N5,list_header_id,3
22128: INDX,QP_PREQ_GRP.select_modifiers.l_inner_qual_cur,QP_PREQ_QUAL_TMP_N5,list_line_id,4
22129:
22130: INDX,QP_PREQ_GRP.select_modifiers.l_inner_qual_cur,QP_LIST_LINES_N5,PRICING_PHASE_ID,1
22131: INDX,QP_PREQ_GRP.select_modifiers.l_inner_qual_cur,QP_LIST_LINES_N5,QUALIFICATION_IND,2
22132: */
22133: CURSOR l_INNER_QUAL_CUR(p_pricing_phase_id NUMBER,
22134: p_list_header_id NUMBER,
22127: INDX,QP_PREQ_GRP.select_modifiers.l_inner_qual_cur,QP_PREQ_QUAL_TMP_N5,list_header_id,3
22128: INDX,QP_PREQ_GRP.select_modifiers.l_inner_qual_cur,QP_PREQ_QUAL_TMP_N5,list_line_id,4
22129:
22130: INDX,QP_PREQ_GRP.select_modifiers.l_inner_qual_cur,QP_LIST_LINES_N5,PRICING_PHASE_ID,1
22131: INDX,QP_PREQ_GRP.select_modifiers.l_inner_qual_cur,QP_LIST_LINES_N5,QUALIFICATION_IND,2
22132: */
22133: CURSOR l_INNER_QUAL_CUR(p_pricing_phase_id NUMBER,
22134: p_list_header_id NUMBER,
22135: p_list_line_id NUMBER,
22138: p_start_date_active_first DATE,
22139: p_start_date_active_second DATE,
22140: p_line_type_code VARCHAR2) IS
22141: -- Qualifier Only
22142: -- 5092968, qp_list_lines_n6 replaced with qp_list_lines_pk
22143: select /*+ ORDERED INDEX(qpl,QP_LIST_LINES_PK) l_inner_qual_cur */
22144: qpl.list_header_id,
22145: qpl.list_line_id,
22146: qpl.list_line_type_code,
22139: p_start_date_active_second DATE,
22140: p_line_type_code VARCHAR2) IS
22141: -- Qualifier Only
22142: -- 5092968, qp_list_lines_n6 replaced with qp_list_lines_pk
22143: select /*+ ORDERED INDEX(qpl,QP_LIST_LINES_PK) l_inner_qual_cur */
22144: qpl.list_header_id,
22145: qpl.list_line_id,
22146: qpl.list_line_type_code,
22147: qpq.line_index,
22182: where pricing_status_code = G_STATUS_UNCHANGED
22183: and pricing_phase_id = p_pricing_phase_id
22184: and search_ind = 1
22185: and list_line_id > 0 ) qpq,
22186: qp_list_lines qpl
22187: where
22188: qpl.list_header_id = qpq.list_header_id
22189: and qpl.list_line_id = qpq.list_line_id
22190: and (qpl.modifier_level_code = qpq.line_type_code
22205: G_COUPON_ISSUE,G_TERMS_SUBSTITUTION,G_ITEM_UPGRADE)
22206: or
22207: nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) <> G_YES)
22208: union all
22209: select /*+ ORDERED INDEX(qpl,QP_LIST_LINES_N5) l_inner_qual_cur */
22210: qpl.list_header_id,
22211: qpl.list_line_id,
22212: qpl.list_line_type_code,
22213: qpq.line_index,
22248: where pricing_status_code = G_STATUS_UNCHANGED
22249: and pricing_phase_id = p_pricing_phase_id
22250: and search_ind = 1
22251: and list_line_id < 0 ) qpq,
22252: qp_list_lines qpl
22253: where
22254: qpl.list_header_id = qpq.list_header_id
22255: and (qpl.modifier_level_code = qpq.line_type_code
22256: OR
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 */
22358: WHERE
22359: (qplines.price_flag = G_YES
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
22530: QP_PRICE_REQ_SOURCES_V qprs /* yangli */
22531: WHERE
22532: (qplines.price_flag = G_YES
22611: nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) <> G_YES)
22612: order by 4,2;
22613:
22614: /*
22615: INDX,QP_PREQ_GRP.select_modifiers.l_cur,QP_LIST_LINES_N5,PRICING_PHASE_ID,1
22616: INDX,QP_PREQ_GRP.select_modifiers.l_cur,QP_LIST_LINES_N5,QUALIFICATION_IND,2
22617:
22618: INDX,QP_PREQ_GRP.select_modifiers.l_cur,QP_LIST_HEADERS_B_N2,LIST_HEADER_ID,1
22619: INDX,QP_PREQ_GRP.select_modifiers.l_cur,QP_LIST_HEADERS_B_N2,ACTIVE_FLAG,2
22612: order by 4,2;
22613:
22614: /*
22615: INDX,QP_PREQ_GRP.select_modifiers.l_cur,QP_LIST_LINES_N5,PRICING_PHASE_ID,1
22616: INDX,QP_PREQ_GRP.select_modifiers.l_cur,QP_LIST_LINES_N5,QUALIFICATION_IND,2
22617:
22618: INDX,QP_PREQ_GRP.select_modifiers.l_cur,QP_LIST_HEADERS_B_N2,LIST_HEADER_ID,1
22619: INDX,QP_PREQ_GRP.select_modifiers.l_cur,QP_LIST_HEADERS_B_N2,ACTIVE_FLAG,2
22620: INDX,QP_PREQ_GRP.select_modifiers.l_cur,QP_LIST_HEADERS_B_N2,CURRENCY_CODE,3
22623: INDX,QP_PREQ_GRP.select_modifiers.l_cur,QP_PRICE_REQ_SOURCES_PK,SOURCE_SYSTEM_CODE,2
22624: */
22625: cursor l_CUR (p_pricing_phase_id NUMBER) IS
22626: -- NO PRODUCTS AND NO QUALIFIERS
22627: 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 */
22628: qpl.list_header_id,
22629: qpl.list_line_id,
22630: qpl.list_line_type_code,
22631: qplines.line_index,
22658: qpl.accum_attribute, -- accum range break
22659: qpl.accum_attr_run_src_flag -- accum range break
22660: from
22661: qp_npreq_lines_tmp qplines,
22662: qp_list_lines qpl,
22663: qp_list_headers_b qph,
22664: --qp_price_req_sources qprs
22665: qp_price_req_sources_v qprs /* yangli */
22666: where qph.list_header_id = qpl.list_header_id
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
25616: --AND ((nvl(G_SECURITY_CONTROL, 'OFF') = 'ON' AND G_CURRENT_USER_OP_UNIT = QPH.orig_org_id) OR -- begin security
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
25724: --AND ((nvl(G_SECURITY_CONTROL, 'OFF') = 'ON' AND G_CURRENT_USER_OP_UNIT = QPH.orig_org_id) OR -- begin security
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
25832: --AND ((nvl(G_SECURITY_CONTROL, 'OFF') = 'ON' AND G_CURRENT_USER_OP_UNIT = QPH.orig_org_id) OR -- begin security
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
25959: --security will be built into qp_list_headers_b
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
26114: --security will be built into qp_list_headers_b
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
26269: --security will be built into qp_list_headers_b
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
26439: AND (qpq.qualifier_context <> G_LIST_HEADER_CONTEXT --bug 2799670
26440: OR qpq.qualifier_attribute <> G_PRICELIST_ATTRIBUTE) --bug 2799670
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
26567: AND (qpq.qualifier_context <> G_LIST_HEADER_CONTEXT --bug 2799670
26568: OR qpq.qualifier_attribute <> G_PRICELIST_ATTRIBUTE) --bug 2799670
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
26695: AND (qpq.qualifier_context <> G_LIST_HEADER_CONTEXT --bug 2799670
26696: OR qpq.qualifier_attribute <> G_PRICELIST_ATTRIBUTE) --bug 2799670
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
26843: -- 3594459, context and attribute OR'd together
26844: AND (qpq.qualifier_context <> G_LIST_HEADER_CONTEXT --bug 2799670
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
27018: -- 3594459, context and attribute OR'd together
27019: AND (qpq.qualifier_context <> G_LIST_HEADER_CONTEXT --bug 2799670
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
27193: -- 3594459, context and attribute OR'd together
27194: AND (qpq.qualifier_context <> G_LIST_HEADER_CONTEXT --bug 2799670
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
27345: qph.list_header_id = qpprod.list_header_id
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
27432: WHERE qph.list_header_id = qpl.list_header_id
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
27563: WHERE
27564: qph.list_header_id = qpl.list_header_id
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,
27669: qp_currency_details qcdt
27670: WHERE
32245: AND pricing_status_code <> G_STATUS_NEW;
32246:
32247: --check if there is such a list line with passed in phase id
32248: /*
32249: INDX,QP_PREQ_GRP.price_request.l_chk_phase_exists,QP_LIST_LINES_N5,PRICING_PHASE_ID,1
32250: */
32251:
32252: -- [julin/4742368/4766135] modified to try list_header_phases first; if not found, go to
32253: -- lines/headers; assuming that phase/qual on lines is a better EXISTS filter than
32264: and qplh.active_flag = 'Y')
32265: OR
32266: EXISTS (
32267: select /*+ ORDERED */ 'x'
32268: from qp_list_lines qpll, qp_list_headers_b qplh
32269: where qpll.pricing_phase_id = p_phase_id
32270: and qpll.qualification_ind in (0, 4, 20)
32271: and qpll.list_header_id = qplh.list_header_id
32272: and qplh.active_flag = 'Y');
32270: and qpll.qualification_ind in (0, 4, 20)
32271: and qpll.list_header_id = qplh.list_header_id
32272: and qplh.active_flag = 'Y');
32273: /*
32274: -- julin [3773033]: removing hint /*+ ordered use_nl(qpll) index(qpll QP_LIST_LINES_N5) /
32275: -- bug 3804392 - exclude factor lists as well
32276: CURSOR l_chk_phase_exists(p_phase_id IN PLS_INTEGER) IS
32277: select 'x'
32278: from qp_list_headers_b qplh, qp_list_lines qpll
32274: -- julin [3773033]: removing hint /*+ ordered use_nl(qpll) index(qpll QP_LIST_LINES_N5) /
32275: -- bug 3804392 - exclude factor lists as well
32276: CURSOR l_chk_phase_exists(p_phase_id IN PLS_INTEGER) IS
32277: select 'x'
32278: from qp_list_headers_b qplh, qp_list_lines qpll
32279: where qplh.active_flag = 'Y'
32280: and qplh.list_type_code not in ('PRL', 'AGR', 'PML')
32281: and qpll.list_header_id = qplh.list_header_id
32282: and qpll.pricing_phase_id = p_phase_id
32282: and qpll.pricing_phase_id = p_phase_id
32283: and qpll.qualification_ind in (0, 2, 4, 6, 8, 10, 12, 14, 20, 22, 28, 30)
32284: and rownum = 1;
32285: select 'X' from dual
32286: where exists (select 'X' from qp_list_lines
32287: where pricing_phase_id = p_phase_id
32288: AND modifier_level_code IN ('LINE','LINEGROUP','ORDER') --3169430
32289: and rownum = 1);*/
32290: