[Home] [Help]
400:
401: -- shu new rounding implementation
402: /*+--------------------------------------------------------------------
403: | UPDATE_ROUNDING_FACTOR
404: | to update rounding_factor in the qp_npreq_lines_tmp table
405: +--------------------------------------------------------------------
406: */
407: PROCEDURE UPDATE_ROUNDING_FACTOR(
408: p_mcurr_installed_used_flag IN VARCHAR2,
412: l_routine VARCHAR2(240):= 'Routine:QP_PREQ_GRP.UPDATE_ROUNDING_FACTOR';
413:
414: CURSOR get_price_lists_cur IS
415: SELECT distinct price_list_header_id , currency_code , trunc(pricing_effective_date) pricing_effective_date
416: FROM qp_npreq_lines_tmp
417: where price_flag in ('Y','P')
418: and line_type_code = 'LINE'
419: and rounding_factor is null
420: and price_list_header_id > 0; --[julin/5395256]
473:
474: END LOOP;
475:
476: FORALL i in p_price_list_id_tbl.FIRST .. p_price_list_id_tbl.LAST
477: UPDATE qp_npreq_lines_tmp
478: SET ROUNDING_FACTOR = p_rounding_factor_tbl(i)
479: WHERE PRICE_FLAG in (QP_PREQ_GRP.G_YES, QP_PREQ_GRP.G_PHASE)
480: AND PRICE_LIST_HEADER_ID = p_price_list_id_tbl(i)
481: AND CURRENCY_CODE = p_currency_code_tbl(i)
3244:
3245: BEGIN
3246: x_status_code := FND_API.G_RET_STS_SUCCESS;
3247: /*
3248: INDX,QP_PREQ_GRP.Update_Processing_Order.Update_Processing_Order_upd1,qp_npreq_lines_tmp_U1,LINE_INDEX,1
3249: */
3250:
3251: -- julin [3805113]: using bulk collect and update
3252: OPEN l_Service_Cur;
3258: EXIT WHEN l_line_index_tbl.count = 0;
3259:
3260: -- julin [3805113]: bulk update
3261: FORALL I IN l_line_index_tbl.first..l_line_index_tbl.last
3262: UPDATE qp_npreq_lines_tmp --upd1
3263: SET PROCESSING_ORDER = 1
3264: WHERE LINE_INDEX = l_line_index_tbl(I);
3265:
3266: --Assuming that parent line has it index on related_line_index column
3264: WHERE LINE_INDEX = l_line_index_tbl(I);
3265:
3266: --Assuming that parent line has it index on related_line_index column
3267: /*
3268: INDX,QP_PREQ_GRP.Update_Processing_Order.Update_Processing_Order_upd2,qp_npreq_lines_tmp_U1,LINE_INDEX,1
3269: */
3270: -- julin [3805113]: bulk update
3271: FORALL I IN l_related_line_index_tbl.first..l_related_line_index_tbl.last
3272: UPDATE qp_npreq_lines_tmp --upd2
3268: INDX,QP_PREQ_GRP.Update_Processing_Order.Update_Processing_Order_upd2,qp_npreq_lines_tmp_U1,LINE_INDEX,1
3269: */
3270: -- julin [3805113]: bulk update
3271: FORALL I IN l_related_line_index_tbl.first..l_related_line_index_tbl.last
3272: UPDATE qp_npreq_lines_tmp --upd2
3273: SET PROCESSING_ORDER = 2
3274: WHERE LINE_INDEX = l_related_line_index_tbl(I);
3275:
3276: END LOOP;
3278:
3279: --added for 4900095
3280: --marking the service lines with uom_quantity as null
3281: --this will be used in lumpsum calculation
3282: for cl in (select line_index from qp_npreq_lines_tmp line
3283: where processing_order = 2
3284: and (contract_start_date is not null or contract_end_date is not null)
3285: and uom_quantity is null)
3286: loop
3328: l_routine VARCHAR2(240):='QP_PREQ_GRP.SET_STATUS_CODE';
3329: BEGIN
3330: IF(p_temp_table = 'LINES') THEN
3331: /*
3332: INDX,QP_PREQ_GRP.set_status_code.set_status_code_upd1,qp_npreq_lines_tmp_N1,LINE_INDEX,1
3333: */
3334: -- shulin fix bug1745788, widen substr to 2000
3335: UPDATE qp_npreq_lines_tmp --upd1
3336: SET PRICING_STATUS_CODE = p_status_code,
3331: /*
3332: INDX,QP_PREQ_GRP.set_status_code.set_status_code_upd1,qp_npreq_lines_tmp_N1,LINE_INDEX,1
3333: */
3334: -- shulin fix bug1745788, widen substr to 2000
3335: UPDATE qp_npreq_lines_tmp --upd1
3336: SET PRICING_STATUS_CODE = p_status_code,
3337: PRICING_STATUS_TEXT = substr(p_status_text,1,2000)
3338: WHERE LINE_INDEX = p_line_index;
3339: ELSIF (p_temp_table = 'LDETS' AND p_line_index IS NOT NULL) THEN
3387: l_routine VARCHAR2(240):='QP_PREQ_GRP.SET_PROCESSED_CODE';
3388: BEGIN
3389: IF(p_temp_table = 'LINES') THEN
3390: /*
3391: INDX,QP_PREQ_GRP.set_processed_code.set_processed_code_upd1,qp_npreq_lines_tmp_N1,LINE_INDEX,1
3392: */
3393: UPDATE qp_npreq_lines_tmp --upd1
3394: SET PROCESSED_CODE = p_processed_code
3395: WHERE LINE_INDEX = p_line_index;
3389: IF(p_temp_table = 'LINES') THEN
3390: /*
3391: INDX,QP_PREQ_GRP.set_processed_code.set_processed_code_upd1,qp_npreq_lines_tmp_N1,LINE_INDEX,1
3392: */
3393: UPDATE qp_npreq_lines_tmp --upd1
3394: SET PROCESSED_CODE = p_processed_code
3395: WHERE LINE_INDEX = p_line_index;
3396: ELSIF (p_temp_table = 'ATTRS' AND p_line_index IS NOT NULL
3397: AND p_line_detail_index IS NULL) THEN
3419: |PROCEDURE DELETE_ALL_LINES
3420: |This procedure mark qp_npreq_line_attrs_tmp and qp_npreq_ldets_tmp
3421: |for delete. And mark qp_preq_lines status code as
3422: |whatever calling procedure wanted. Which is usually
3423: |not a 'Delete' because record in qp_npreq_lines_tmp
3424: |is the only one which communicates with outside
3425: |world (order managment for example).
3426: +----------------------------------------------------
3427: */
4259: AS
4260: l_routine VARCHAR2(240):='Routine:QP_PREQ_GRP.INSERT_LINES';
4261: BEGIN
4262: x_status_code := FND_API.G_RET_STS_SUCCESS;
4263: INSERT INTO qp_npreq_lines_tmp
4264: (LINE_INDEX,
4265: LINE_TYPE_CODE,
4266: PRICING_EFFECTIVE_DATE,
4267: START_DATE_ACTIVE_FIRST,
4574:
4575: x_status_code := FND_API.G_RET_STS_SUCCESS;
4576:
4577: FORALL i in p_line_index.FIRST..p_line_index.LAST
4578: INSERT INTO qp_npreq_lines_tmp
4579: (LINE_INDEX,
4580: LINE_TYPE_CODE,
4581: PRICING_EFFECTIVE_DATE,
4582: START_DATE_ACTIVE_FIRST,
5108: --added by yangli for Java Engine project
5109:
5110: FORALL i in p_line_index.FIRST..p_line_index.LAST
5111:
5112: INSERT INTO qp_npreq_lines_tmp
5113: (LINE_INDEX,
5114: LINE_TYPE_CODE,
5115: PRICING_EFFECTIVE_DATE,
5116: START_DATE_ACTIVE_FIRST,
7353: LINE_TYPE_CODE,
7354: LINE_UOM_CODE,
7355: PRICED_UOM_CODE,
7356: PRICE_FLAG
7357: FROM qp_npreq_lines_tmp;
7358: --WHERE LINE_UOM_CODE <> PRICED_UOM_CODE;
7359: /*WHERE (PRICE_FLAG = G_YES
7360: OR
7361: (PRICE_FLAG = G_PHASE
7384: */
7385: CURSOR order_amount_cur IS
7386: --SELECT nvl(SUM(UNIT_PRICE * PRICED_QUANTITY),0)
7387: select nvl(sum(nvl(line_unit_price * line_quantity, UNIT_PRICE * PRICED_QUANTITY)),0) /* 3143535 */
7388: FROM qp_npreq_lines_tmp
7389: WHERE charge_periodicity_code is NULL; -- added for recurring charges Bug # 4465168
7390:
7391:
7392:
7474: END IF;
7475: END IF;
7476: -- Reset the unit price if price event occurs
7477: IF (i.PRICE_FLAG = G_YES OR (i.PRICE_FLAG = G_PHASE AND p_freeze_override_flag = G_YES)) THEN
7478: UPDATE qp_npreq_lines_tmp
7479: SET UNIT_PRICE = NULL
7480: WHERE LINE_INDEX = I.LINE_INDEX;
7481: END IF;
7482:
7598: x_status_text =>l_status_text);
7599: END IF; */ -- Attribute Mapping is sourcing Volume Attributes 01/14/02, AC Nielsen Performance
7600:
7601:
7602: /*UPDATE qp_npreq_lines_tmp
7603: SET LINE_AMOUNT = nvl(nvl(I.PRICED_QUANTITY,I.LINE_QUANTITY) * I.UNIT_PRICE,0)
7604: WHERE LINE_INDEX = I.LINE_INDEX; */
7605:
7606: G_QUALIFIER_TEMP := 'N';
7684: G_NO,
7685: G_NO,
7686: G_STATUS_UNCHANGED,
7687: G_NO
7688: FROM qp_npreq_lines_tmp;
7689:
7690: END IF;
7691:
7692: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
9071: l_pricing_effective_date Date;
9072: l_debug varchar2(3);
9073:
9074: /*
9075: INDX,QP_PREQ_GRP.set_expiration_dates.l_get_pricing_effective_date,qp_npreq_lines_tmp_N1,LINE_INDEX,1
9076: */
9077: CURSOR l_get_pricing_effective_date (p_line_index IN PLS_INTEGER) IS
9078: SELECT pricing_effective_date
9079: FROM qp_npreq_lines_tmp
9075: INDX,QP_PREQ_GRP.set_expiration_dates.l_get_pricing_effective_date,qp_npreq_lines_tmp_N1,LINE_INDEX,1
9076: */
9077: CURSOR l_get_pricing_effective_date (p_line_index IN PLS_INTEGER) IS
9078: SELECT pricing_effective_date
9079: FROM qp_npreq_lines_tmp
9080: WHERE line_index = p_line_index;
9081:
9082: --added by yangli for Java Engine interface tables
9083: /*
9190: EXTENDED_PRICE /* block pricing */
9191: --EXTENDED_SELLING_PRICE_UR, --[prarasto:Post Round]New fields for unrounded values, [julin/postround] redesign
9192: --ORDER_UOM_SELLING_PRICE_UR, --[prarasto:Post Round]
9193: --ADJUSTED_UNIT_PRICE_UR --[prarasto:Post Round]
9194: FROM qp_npreq_lines_tmp;
9195:
9196: /*
9197: INDX,QP_PREQ_GRP.populate_output.l_qual_cur,qp_npreq_ldets_tmp_N4,PRICING_STATUS_CODE,1
9198:
10268: |across all lines that have same product context,attribute,value
10269: |, pricing context,attribute and have list_line which is
10270: |line group. The output on the sum will be populated
10271: |either on GROUP_QUANTITY or qualifier_amt column
10272: |of qp_npreq_lines_tmp.
10273: +--------------------------------------------------------
10274: */
10275:
10276: PROCEDURE PROCESS_LINE_GROUP(p_pricing_phase_id NUMBER,
10316:
10317: --begin 2388011, grp_pbh_amt
10318: CURSOR l_get_list_price_cur(p_list_line_id NUMBER) IS
10319: SELECT a.LINE_INDEX ,a.UNIT_PRICE
10320: FROM qp_npreq_lines_tmp a,qp_npreq_ldets_tmp b
10321: WHERE a.LINE_INDEX = b.LINE_INDEX
10322: AND b.CREATED_FROM_LIST_LINE_ID = p_list_line_id;
10323: --end 2388011, grp_pbh_amt
10324:
10521: engine_debug('p_line_index : '||p_line_index);
10522: engine_debug('p_product_uom_code: '||nvl(p_product_uom_code,'Null'));
10523: END IF;
10524: /*
10525: INDX,QP_PREQ_GRP.is_uom_match.is_uom_match_sel1,qp_npreq_lines_tmp_N1,LINE_INDEX,1
10526: */
10527: SELECT 'X'
10528: INTO l_dummy
10529: FROM qp_npreq_lines_tmp qplines
10525: INDX,QP_PREQ_GRP.is_uom_match.is_uom_match_sel1,qp_npreq_lines_tmp_N1,LINE_INDEX,1
10526: */
10527: SELECT 'X'
10528: INTO l_dummy
10529: FROM qp_npreq_lines_tmp qplines
10530: WHERE qplines.LINE_INDEX = p_line_index
10531: AND nvl(qplines.PRICED_UOM_CODE,qplines.LINE_UOM_CODE) =
10532: nvl(p_product_uom_code,nvl(qplines.PRICED_UOM_CODE,qplines.LINE_UOM_CODE));
10533:
10612: engine_debug('In Update Request Line Status : ' || p_status_code);
10613: END IF;
10614:
10615: /* --po integration start
10616: UPDATE qp_npreq_lines_tmp a
10617: SET pricing_status_code = G_STATUS_UPDATED
10618: Where list_price_override_flag in ('Y', 'O') --po integration
10619: AND line_type_code <> G_ORDER_LEVEL;
10620: --po integration end
10630: INDX,QP_PREQ_GRP.update_request_line_status.sel1,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
10631: INDX,QP_PREQ_GRP.update_request_line_status.sel1,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
10632: */
10633:
10634: UPDATE qp_npreq_lines_tmp a
10635: SET pricing_status_code = G_STATUS_UPDATED
10636: ,processed_code = NULL -- bug 3501150
10637: WHERE EXISTS (SELECT 'x'
10638: from qp_npreq_ldets_tmp b
10641: AND b.pricing_phase_id = G_PRICE_LIST_PHASE_ID)
10642: AND line_type_code <> G_ORDER_LEVEL;
10643:
10644: -- To make sure lines which did not get the price and do not have a record in qp_npreq_ldets_tmp get picked up
10645: UPDATE qp_npreq_lines_tmp a
10646: SET processed_code = G_STS_LHS_NOT_FOUND
10647: WHERE NOT EXISTS (SELECT 'x'
10648: FROM qp_npreq_ldets_tmp b
10649: WHERE a.line_index = b.line_index
10662:
10663: DECLARE
10664:
10665: CURSOR tmp_data IS
10666: SELECT rounding_factor FROM qp_npreq_lines_tmp;
10667: BEGIN
10668:
10669: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
10670: engine_debug ('G_MCURR_PROFILE: '||G_MULTI_CURRENCY_PROFILE);
10676: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
10677: engine_debug ('get rounding_factor from qp_currency_details table...');
10678: END IF;
10679:
10680: UPDATE qp_npreq_lines_tmp a
10681: SET ROUNDING_FACTOR = (SELECT qcdt.selling_rounding_factor
10682: FROM qp_currency_details qcdt, qp_list_headers_b qhdr
10683: WHERE qhdr.list_header_id = a.price_list_header_id
10684: AND qhdr.currency_header_id = qcdt.currency_header_id
10693:
10694: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
10695: engine_debug('get rounding factor from qp_list_headers_b...');
10696: END IF;
10697: UPDATE qp_npreq_lines_tmp a
10698: SET ROUNDING_FACTOR = (SELECT qhdr.rounding_factor
10699: FROM qp_list_headers_b qhdr
10700: WHERE qhdr.list_header_id = a.price_list_header_id)
10701: WHERE ROUNDING_FACTOR IS NULL;
10729: INDX,QP_PREQ_GRP.update_request_line_status.sel3,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
10730: INDX,QP_PREQ_GRP.update_request_line_status.sel3,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
10731: */
10732:
10733: UPDATE qp_npreq_lines_tmp a
10734: SET processed_code = G_STS_LHS_NOT_FOUND
10735: WHERE NOT EXISTS (SELECT 'x'
10736: FROM qp_npreq_ldets_tmp b
10737: WHERE a.line_index = b.line_index
10917: IS
10918: SELECT /*+ ORDERED USE_NL(a) l_line_amount_cur */
10919: fnd_number.canonical_to_number(c.value_from) group_value,
10920: a.priced_quantity
10921: FROM qp_npreq_line_attrs_tmp c, qp_npreq_lines_tmp a
10922: WHERE a.line_index = p_line_index
10923: AND a.LINE_INDEX = c.LINE_INDEX
10924: AND c.line_detail_index = p_line_detail_index
10925: AND c.context = p_context
11140:
11141: -- begin shu, fix group of lines lumpsum based on ITEM_AMOUNT
11142:
11143: SELECT UNIT_PRICE INTO l_list_price
11144: FROM qp_npreq_lines_tmp
11145: WHERE LINE_INDEX = i.line_index;
11146:
11147: /* Handled l_list_price = 0 bug2385874 */
11148: IF (l_list_price IS NOT NULL and l_list_price <>0) THEN -- avoid division by zero
11308: -- [julin/5085310]
11309: IF (i.attribute = G_LINE_AMT_ATTRIBUTE) THEN
11310:
11311: SELECT UNIT_PRICE INTO l_list_price
11312: FROM qp_npreq_lines_tmp
11313: WHERE LINE_INDEX = i.line_index;
11314:
11315: /* Handled l_list_price = 0 bug2385874 */
11316: IF (l_list_price IS NOT NULL and l_list_price <>0) THEN -- avoid division by zero
11425: INDX,QP_PREQ_GRP.handle_break.l_price_break_detail_cur,qp_npreq_line_attrs_tmp_N2,CONTEXT,3
11426: INDX,QP_PREQ_GRP.handle_break.l_price_break_detail_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE,4
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:
11484: qplines.contract_start_date, -- R12 partial period pricing
11485: qplines.contract_end_date -- R12 partial period pricing
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 */
11615: qplines.contract_start_date, -- R12 partial period pricing
11616: qplines.contract_end_date -- R12 partial period pricing
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
12157: -- call the oks api only once
12158: if l_first_time_flag = 'Y' then
12159: select contract_start_date, contract_end_date
12160: into l_contract_start_date, l_contract_end_date
12161: from qp_npreq_lines_tmp
12162: where line_index = p_line_index;
12163:
12164: -- OKS_QP_INT.get_conversion_factor was replaced by
12165: -- OKS_OMINT_PUB.get_quantity in R12 for partial period pricing
12993: -- if price break price lists are deleted at this point , then mark the status on the line for
12994: -- secondary or big search. Do this only for pricing phase
12995: IF (g_failed_ld_tbl_g.count > 0 and p_pricing_phase_id = G_PRICE_LIST_PHASE_ID) THEN
12996: -- To make sure lines which did not get the price and do not have a record in qp_npreq_ldets_tmp get picked up
12997: UPDATE qp_npreq_lines_tmp a
12998: SET processed_code = G_STS_LHS_NOT_FOUND
12999: WHERE NOT EXISTS (SELECT 'x'
13000: FROM qp_npreq_ldets_tmp b
13001: WHERE a.line_index = b.line_index
14312: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel1,qp_npreq_line_attrs_tmp_N1,ATTRIBUTE_TYPE,2
14313: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel1,qp_npreq_line_attrs_tmp_N1,PRICING_STATUS_CODE,3
14314: */
14315: IF (G_PRICE_PHASE_FLAG = TRUE ) THEN -- Ravi
14316: UPDATE /*+ dynamic_sampling(1) */ qp_npreq_lines_tmp a
14317: SET processed_code = G_NO_LIST_PASSED,
14318: validated_flag = G_NO
14319: WHERE not exists (SELECT 'x'
14320: FROM qp_npreq_line_attrs_tmp b
14331: engine_debug ('after update1...');
14332: END IF;
14333:
14334: /*
14335: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_upd3,qp_npreq_lines_tmp_N3,VALIDATED_FLAG,1
14336: */
14337: /*
14338: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel2,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
14339: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel2,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
14345: IF (G_PRICE_PHASE_FLAG = TRUE ) THEN -- Ravi
14346:
14347: --[julin/pricebook] added hint
14348: --[julin/5223502] price_list_header_id directly passed in via lines_tmp has precedence over sourced value
14349: UPDATE /*+ dynamic_sampling(1) */ qp_npreq_lines_tmp a
14350: SET (price_list_header_id,validated_flag) =
14351: (SELECT /*+ index(b QP_PREQ_LINE_ATTRS_TMP_N2) */ distinct
14352: CASE WHEN (a.PRICE_LIST_HEADER_ID < 0) THEN qp_number.canonical_to_number(value_from)
14353: ELSE a.PRICE_LIST_HEADER_ID
14375: IF (G_PRICE_PHASE_FLAG = TRUE AND G_MANUAL_ADJUSTMENTS_CALL_FLAG = G_NO) THEN
14376: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
14377: engine_debug('Trying to Update Unit Price To Null');
14378: END IF;
14379: UPDATE qp_npreq_lines_tmp a
14380: SET unit_price = NULL
14381: WHERE (price_flag = G_YES
14382: OR
14383: (price_flag = G_PHASE
14391: engine_debug ('after update3');
14392: END IF;
14393:
14394: /*
14395: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_upd4,qp_npreq_lines_tmp_N3,VALIDATED_FLAG,1
14396: */
14397: /*
14398: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel3,QP_QUALIFIERS_N7,QUALIFIER_CONTEXT,1
14399: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel3,QP_QUALIFIERS_N7,QUALIFIER_ATTRIBUTE,2
14399: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel3,QP_QUALIFIERS_N7,QUALIFIER_ATTRIBUTE,2
14400: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel3,QP_QUALIFIERS_N7,LIST_HEADER_ID,3
14401: */
14402: IF (G_PRICE_PHASE_FLAG = TRUE) THEN -- Ravi,
14403: UPDATE qp_npreq_lines_tmp a
14404: SET qualifiers_exist_flag = G_YES
14405: WHERE price_list_header_id IS NOT NULL
14406: AND validated_flag = G_NO
14407: AND EXISTS (SELECT 'x'
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
14420: WHERE price_list_header_id IS NOT NULL
14421: AND EXISTS
14422: (SELECT 'x'
14462: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel1,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE,4
14463: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel1,qp_npreq_line_attrs_tmp_N2,LINE_INDEX,5
14464: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel1,qp_npreq_line_attrs_tmp_N2,VALUE_FROM,6
14465: */
14466: UPDATE qp_npreq_lines_tmp c
14467: SET primary_qualifiers_match_flag = 'Y'
14468: WHERE EXISTS(
14469: SELECT 'x'
14470: FROM QP_QUALIFIERS a,
14495: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel2,qp_npreq_line_attrs_tmp_N2,CONTEXT,3
14496: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel2,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE,4
14497: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel2,qp_npreq_line_attrs_tmp_N2,LINE_INDEX,5
14498: */
14499: UPDATE qp_npreq_lines_tmp c
14500: SET primary_qualifiers_match_flag = 'Y'
14501: WHERE EXISTS(
14502: SELECT 'x'
14503: FROM qp_npreq_line_attrs_tmp b ,QP_QUALIFIERS a
14513: AND b.LINE_INDEX = c.LINE_INDEX
14514: AND c.VALIDATED_FLAG = G_NO)
14515: AND c.VALIDATED_FLAG = G_NO ;
14516:
14517: UPDATE qp_npreq_lines_tmp c
14518: SET primary_qualifiers_match_flag = 'Y'
14519: WHERE EXISTS(
14520: SELECT 'x'
14521: FROM qp_npreq_line_attrs_tmp b ,QP_QUALIFIERS a
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
14582:
14583: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_LIST_HEADERS_B_PK,LINE_TYPE_CODE,2
14584:
14585: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
14642: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
14643: ,NULL QUALIFIER_TYPE -- 3863226
14644: ,QPLAT_PROD.DERIVED_QUALIFIER_FLAG -- Added for TCA
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,
14763: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
14764: ,NULL QUALIFIER_TYPE -- 3863226
14765: ,QPLAT_PRIC.DERIVED_QUALIFIER_FLAG -- Added for TCA
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,
14890: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
14891: ,DECODE(QPQ.list_line_id, -1, 'HQ', 'LQ') QUALIFIER_TYPE -- 3863226
14892: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
14893: FROM
14894: qp_npreq_lines_tmp QPLINES,
14895: QP_QUALIFIERS QPQ,
14896: QP_QUALIFIERS QPSQ,
14897: qp_npreq_line_attrs_tmp QPLAT_PROD,
14898: qp_npreq_line_attrs_tmp QPLAT_Q,
15031: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
15032: ,DECODE(QPQ.list_line_id, -1, 'HQ', 'LQ') QUALIFIER_TYPE -- 3863226
15033: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
15034: FROM
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,
15189: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
15190: ,NULL QUALIFIER_TYPE -- 3863226
15191: ,QPLAT_PROD.DERIVED_QUALIFIER_FLAG -- Added for TCA
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,
15370: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
15371: ,NULL QUALIFIER_TYPE -- 3863226
15372: ,QPLAT_PRIC.DERIVED_QUALIFIER_FLAG -- Added for TCA
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,
15557: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
15558: ,DECODE(QPQ.list_line_id, -1, 'HQ', 'LQ') QUALIFIER_TYPE --3863226
15559: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
15560: FROM
15561: qp_npreq_lines_tmp QPLINES,
15562: QP_QUALIFIERS QPQ,
15563: QP_QUALIFIERS QPSQ,
15564: qp_npreq_line_attrs_tmp QPLAT_PROD,
15565: qp_npreq_line_attrs_tmp QPLAT_Q,
15758: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
15759: ,DECODE(QPQ.list_line_id, -1, 'HQ', 'LQ') QUALIFIER_TYPE --3863226
15760: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
15761: FROM
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,
16696: -- 3773652
16697: -- re-run Evaluate_Between after UOM conversion to check PLL really qualifies
16698: begin
16699: select 'x' into l_dummy
16700: from qp_npreq_lines_tmp
16701: where LINE_UOM_CODE <> nvl(PRICED_UOM_CODE, LINE_UOM_CODE)
16702: and (PRICE_FLAG = G_YES
16703: or
16704: PRICE_FLAG = G_PHASE and p_freeze_override_flag = G_YES)
16787: x_status_code OUT NOCOPY VARCHAR2,
16788: x_status_text OUT NOCOPY VARCHAR2)
16789: AS
16790: /*
16791: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,qp_npreq_lines_tmp_N3,VALIDATED_FLAG,1
16792: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,qp_npreq_lines_tmp_N3,QUALIFIERS_EXIST_FLAG,2
16793: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,qp_npreq_lines_tmp_N3,PRICING_ATTRS_EXIST_FLAG,3
16794:
16795: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,qp_npreq_line_attrs_tmp_N1,LINE_INDEX,1
16788: x_status_text OUT NOCOPY VARCHAR2)
16789: AS
16790: /*
16791: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,qp_npreq_lines_tmp_N3,VALIDATED_FLAG,1
16792: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,qp_npreq_lines_tmp_N3,QUALIFIERS_EXIST_FLAG,2
16793: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,qp_npreq_lines_tmp_N3,PRICING_ATTRS_EXIST_FLAG,3
16794:
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
16789: AS
16790: /*
16791: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,qp_npreq_lines_tmp_N3,VALIDATED_FLAG,1
16792: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,qp_npreq_lines_tmp_N3,QUALIFIERS_EXIST_FLAG,2
16793: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,qp_npreq_lines_tmp_N3,PRICING_ATTRS_EXIST_FLAG,3
16794:
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
16852: ,QPL.BREAK_UOM_CONTEXT /* Proration */
16853: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
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,
16957: ,QPL.BREAK_UOM_CONTEXT /* Proration */
16958: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
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,
17082: ,QPL.BREAK_UOM_CONTEXT /* Proration */
17083: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
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,
17239: ,QPL.BREAK_UOM_CONTEXT /* Proration */
17240: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
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,
17362: ORDER BY 1,2; --ORDER BY LINE_INDEX AND LIST LINE ID
17363:
17364:
17365: /*
17366: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_lines_tmp_N3,VALIDATED_FLAG,1
17367: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_lines_tmp_N3,QUALIFIERS_EXIST_FLAG,2
17368: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_lines_tmp_N3,PRICING_ATTRS_EXIST_FLAG,3
17369:
17370: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
17363:
17364:
17365: /*
17366: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_lines_tmp_N3,VALIDATED_FLAG,1
17367: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_lines_tmp_N3,QUALIFIERS_EXIST_FLAG,2
17368: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_lines_tmp_N3,PRICING_ATTRS_EXIST_FLAG,3
17369:
17370: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
17371: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
17364:
17365: /*
17366: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_lines_tmp_N3,VALIDATED_FLAG,1
17367: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_lines_tmp_N3,QUALIFIERS_EXIST_FLAG,2
17368: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_lines_tmp_N3,PRICING_ATTRS_EXIST_FLAG,3
17369:
17370: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
17371: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
17372: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_QUALIFIERS_N1,ACTIVE_FLAG,6
17455: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
17456: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
17457: ,QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
17458: FROM
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,
17582: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
17583: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
17584: ,QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
17585: FROM
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,
17718: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
17719: ,qplat_prod.DERIVED_QUALIFIER_FLAG -- Added for TCA
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,
17820: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
17821: ,qplat_pric.DERIVED_QUALIFIER_FLAG -- Added for TCA
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,
17941: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
17942: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
17943: ,QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
17944: FROM
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,
18126: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
18127: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
18128: ,QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
18129: FROM
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,
18320: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
18321: ,qplat_prod.DERIVED_QUALIFIER_FLAG -- Added for TCA
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,
18483: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
18484: ,qplat_pric.DERIVED_QUALIFIER_FLAG -- Added for TCA
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,
18602: INDX,QP_PREQ_GRP.list_header_search.l_validated_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
18603: INDX,QP_PREQ_GRP.list_header_search.l_validated_cur,qp_npreq_line_attrs_tmp_N2,CONTEXT,3
18604: INDX,QP_PREQ_GRP.list_header_search.l_validated_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE,4
18605:
18606: INDX,QP_PREQ_GRP.list_header_search.l_validated_cur,qp_npreq_lines_tmp_U1,LINE_INDEX,1
18607: INDX,QP_PREQ_GRP.list_header_search.l_validated_cur,qp_npreq_lines_tmp_U1,LINE_TYPE_CODE,2
18608: */
18609:
18610: CURSOR l_validated_cur IS
18603: INDX,QP_PREQ_GRP.list_header_search.l_validated_cur,qp_npreq_line_attrs_tmp_N2,CONTEXT,3
18604: INDX,QP_PREQ_GRP.list_header_search.l_validated_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE,4
18605:
18606: INDX,QP_PREQ_GRP.list_header_search.l_validated_cur,qp_npreq_lines_tmp_U1,LINE_INDEX,1
18607: INDX,QP_PREQ_GRP.list_header_search.l_validated_cur,qp_npreq_lines_tmp_U1,LINE_TYPE_CODE,2
18608: */
18609:
18610: CURSOR l_validated_cur IS
18611: SELECT /*+ dynamic_sampling(1) */ LINE_INDEX,
18625: AND ATTRIBUTE IN (G_PROMOTION_ATTRIBUTE,G_QUAL_ATTRIBUTE2)
18626: AND ATTRIBUTE_TYPE = G_QUALIFIER_TYPE
18627: AND EXISTS
18628: (Select /*+ dynamic_sampling(1) */ 'X'
18629: FROM qp_npreq_lines_tmp b
18630: WHERE b.line_index = a.line_index
18631: AND (b.price_flag = G_YES
18632: OR
18633: (b.price_flag = G_PHASE
18645: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,qp_npreq_line_attrs_tmp_N1,LINE_INDEX,1
18646: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,qp_npreq_line_attrs_tmp_N1,ATTRIBUTE_TYPE,2
18647: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,qp_npreq_line_attrs_tmp_N1,PRICING_STATUS_CODE,3
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
18646: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,qp_npreq_line_attrs_tmp_N1,ATTRIBUTE_TYPE,2
18647: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,qp_npreq_line_attrs_tmp_N1,PRICING_STATUS_CODE,3
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
18719: NULL PRICING_ATTRIBUTE_TYPE,
18720: NULL PRICING_OPERATOR_TYPE,
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,
18834: QPLAT_PRIC.ATTRIBUTE_TYPE PRICING_ATTRIBUTE_TYPE,
18835: QPA.COMPARISON_OPERATOR_CODE PRICING_OPERATOR_TYPE,
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,
18963: NULL PRICING_ATTRIBUTE_TYPE,
18964: NULL PRICING_OPERATOR_TYPE,
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,
19078: QPLAT_PRIC.ATTRIBUTE_TYPE PRICING_ATTRIBUTE_TYPE,
19079: QPA.COMPARISON_OPERATOR_CODE PRICING_OPERATOR_TYPE,
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,
19157: INDX,QP_PREQ_GRP.list_header_search.l_check_no_list_passed,- No Index Used-,NA,NA
19158: */
19159: CURSOR L_CHECK_NO_LIST_PASSED IS
19160: SELECT 'X'
19161: FROM qp_npreq_lines_tmp A
19162: WHERE PROCESSED_CODE IN (G_NO_LIST_PASSED,G_STS_LHS_NOT_FOUND)
19163: AND LINE_TYPE_CODE <> G_ORDER_LEVEL
19164: AND (instr(PROCESS_STATUS,'FREEGOOD')=0); -- bug 2693700
19165:
19167: INDX,QP_PREQ_GRP.list_header_search.l_check_secondary_search,- No Index Used-,NA,NA
19168: */
19169: CURSOR l_check_secondary_search IS
19170: SELECT 'X'
19171: FROM qp_npreq_lines_tmp
19172: WHERE PROCESSED_CODE = G_STS_LHS_NOT_FOUND
19173: AND LINE_TYPE_CODE <> G_ORDER_LEVEL
19174: AND (instr(PROCESS_STATUS,'FREEGOOD')=0); -- bug 2693700
19175:
20581: -- 3773652
20582: -- re-run Evaluate_Between after UOM conversion to check PLL really qualifies
20583: begin
20584: select 'x' into l_dummy
20585: from qp_npreq_lines_tmp
20586: where LINE_UOM_CODE <> nvl(PRICED_UOM_CODE, LINE_UOM_CODE)
20587: and (PRICE_FLAG = G_YES
20588: or
20589: PRICE_FLAG = G_PHASE and p_freeze_override_flag = G_YES)
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:
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
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
21730: and qpl.list_line_id = qpprod.list_line_id
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
21819: and (qpl.modifier_level_code = qplines.line_type_code
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:
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
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:
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
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: */
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
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: */
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
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
21973: and qpl.list_line_id = qpprod.list_line_id
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
22086: and (qpl.modifier_level_code = qplines.line_type_code
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
22294: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,qp_npreq_lines_tmp_N1,LINE_TYPE_CODE,2
22295:
22296: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
22297: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICE_REQ_SOURCES_PK,SOURCE_SYSTEM_CODE,2
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
22294: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,qp_npreq_lines_tmp_N1,LINE_TYPE_CODE,2
22295:
22296: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
22297: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICE_REQ_SOURCES_PK,SOURCE_SYSTEM_CODE,2
22298: */
22347: qpl.accum_context, -- accum range break
22348: qpl.accum_attribute, -- accum range break
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 ,
22451: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,qp_npreq_line_attrs_tmp_N2,LINE_INDEX,5
22452:
22453: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
22454:
22455: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
22456: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,qp_npreq_lines_tmp_N1,LINE_TYPE_CODE,2
22457:
22458: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
22459: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICE_REQ_SOURCES_PK,SOURCE_SYSTEM_CODE,2
22452:
22453: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
22454:
22455: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
22456: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,qp_npreq_lines_tmp_N1,LINE_TYPE_CODE,2
22457:
22458: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
22459: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICE_REQ_SOURCES_PK,SOURCE_SYSTEM_CODE,2
22460: */
22519: qpl.accum_context, -- accum range break
22520: qpl.accum_attribute, -- accum range break
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 ,
22657: qpl.accum_context, -- accum range break
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 */
22814: INDX,QP_PREQ_GRP.select_modifiers.l_get_line_index_for_quals,qp_npreq_line_attrs_tmp_N2,VALUE_FROM,6
22815:
22816: INDX,QP_PREQ_GRP.select_modifiers.l_get_line_index_for_quals,QP_PREQ_QUAL_TMP_N3,PRICING_STATUS_CODE,1
22817:
22818: -- Full table scan on qp_npreq_lines_tmp
22819: */
22820:
22821: --hw
22822:
22862: NULL ACTIVE_DATE_FIRST_TYPE,
22863: NULL ACTIVE_DATE_SECOND_TYPE,
22864: c.HEADER_LIMIT_EXISTS,
22865: a.DERIVED_QUALIFIER_FLAG -- Added for TCA
22866: FROM qp_npreq_lines_tmp b,
22867: QP_PREQ_QUAL_TMP c ,
22868: qp_npreq_line_attrs_tmp a
22869: WHERE a.CONTEXT = c.QUALIFIER_ATTRIBUTE_CONTEXT
22870: AND a.ATTRIBUTE = c.QUALIFIER_ATTRIBUTE
22953: NULL END_DATE_ACTIVE_SECOND_S,
22954: NULL ACTIVE_DATE_FIRST_TYPE,
22955: NULL ACTIVE_DATE_SECOND_TYPE,
22956: c.HEADER_LIMIT_EXISTS
22957: FROM qp_npreq_lines_tmp b,
22958: QP_PREQ_QUAL_TMP c ,
22959: qp_npreq_line_attrs_tmp a
22960: WHERE a.CONTEXT = c.QUALIFIER_ATTRIBUTE_CONTEXT
22961: AND a.ATTRIBUTE = c.QUALIFIER_ATTRIBUTE
23043: NULL END_DATE_ACTIVE_SECOND_S,
23044: NULL ACTIVE_DATE_FIRST_TYPE,
23045: NULL ACTIVE_DATE_SECOND_TYPE,
23046: c.HEADER_LIMIT_EXISTS
23047: FROM qp_npreq_lines_tmp b,
23048: QP_PREQ_QUAL_TMP c ,
23049: qp_npreq_line_attrs_tmp a
23050: WHERE a.CONTEXT = c.QUALIFIER_ATTRIBUTE_CONTEXT
23051: AND a.ATTRIBUTE = c.QUALIFIER_ATTRIBUTE
25599: ,QPL.BREAK_UOM_CONTEXT /* Proration */
25600: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
25601: FROM --[julin/5007789]
25602: (SELECT /*+ dynamic_sampling(1) */ *
25603: FROM qp_npreq_lines_tmp
25604: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
25605: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
25606: qp_npreq_line_attrs_tmp qplatq,
25607: qp_qualifiers qpq,
25707: ,QPL.BREAK_UOM_CONTEXT /* Proration */
25708: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
25709: FROM --[julin/5007789]
25710: (SELECT /*+ dynamic_sampling(1) */ *
25711: FROM qp_npreq_lines_tmp
25712: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
25713: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
25714: qp_npreq_line_attrs_tmp qplatq,
25715: qp_qualifiers qpq,
25815: ,QPL.BREAK_UOM_CONTEXT /* Proration */
25816: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
25817: FROM --[julin/5007789]
25818: (SELECT /*+ dynamic_sampling(1) */ *
25819: FROM qp_npreq_lines_tmp
25820: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
25821: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
25822: qp_npreq_line_attrs_tmp qplatq,
25823: qp_qualifiers qpq,
25942: ,QPL.BREAK_UOM_CONTEXT /* Proration */
25943: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
25944: FROM --[julin/5007789]
25945: (SELECT /*+ dynamic_sampling(1) */ *
25946: FROM qp_npreq_lines_tmp
25947: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
25948: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
25949: qp_npreq_line_attrs_tmp qplatq,
25950: qp_qualifiers qpq,
26097: ,QPL.BREAK_UOM_CONTEXT /* Proration */
26098: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
26099: FROM --[julin/5007789]
26100: (SELECT /*+ dynamic_sampling(1) */ *
26101: FROM qp_npreq_lines_tmp
26102: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
26103: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
26104: qp_npreq_line_attrs_tmp qplatq,
26105: qp_qualifiers qpq,
26252: ,QPL.BREAK_UOM_CONTEXT /* Proration */
26253: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
26254: FROM --[julin/5007789]
26255: (SELECT /*+ dynamic_sampling(1) */ *
26256: FROM qp_npreq_lines_tmp
26257: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
26258: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
26259: qp_npreq_line_attrs_tmp qplatq,
26260: qp_qualifiers qpq,
26422: ,QPL.BREAK_UOM_CONTEXT /* Proration */
26423: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
26424: FROM --[julin/5007789]
26425: (SELECT /*+ dynamic_sampling(1) */ *
26426: FROM qp_npreq_lines_tmp
26427: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
26428: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
26429: qp_npreq_line_attrs_tmp qplatq,
26430: qp_qualifiers qpq ,
26550: ,QPL.BREAK_UOM_CONTEXT /* Proration */
26551: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
26552: FROM --[julin/5007789]
26553: (SELECT /*+ dynamic_sampling(1) */ *
26554: FROM qp_npreq_lines_tmp
26555: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
26556: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
26557: qp_npreq_line_attrs_tmp qplatq,
26558: qp_qualifiers qpq ,
26678: ,QPL.BREAK_UOM_CONTEXT /* Proration */
26679: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
26680: FROM --[julin/5007789]
26681: (SELECT /*+ dynamic_sampling(1) */ *
26682: FROM qp_npreq_lines_tmp
26683: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
26684: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
26685: qp_npreq_line_attrs_tmp qplatq,
26686: qp_qualifiers qpq ,
26826: ,QPL.BREAK_UOM_CONTEXT /* Proration */
26827: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
26828: FROM --[julin/5007789]
26829: (SELECT /*+ dynamic_sampling(1) */ *
26830: FROM qp_npreq_lines_tmp
26831: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
26832: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
26833: qp_npreq_line_attrs_tmp qplatq,
26834: qp_qualifiers qpq ,
27001: ,QPL.BREAK_UOM_CONTEXT /* Proration */
27002: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
27003: FROM --[julin/5007789]
27004: (SELECT /*+ dynamic_sampling(1) */ *
27005: FROM qp_npreq_lines_tmp
27006: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
27007: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
27008: qp_npreq_line_attrs_tmp qplatq,
27009: qp_qualifiers qpq ,
27176: ,QPL.BREAK_UOM_CONTEXT /* Proration */
27177: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
27178: FROM --[julin/5007789]
27179: (SELECT /*+ dynamic_sampling(1) */ *
27180: FROM qp_npreq_lines_tmp
27181: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
27182: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
27183: qp_npreq_line_attrs_tmp qplatq,
27184: qp_qualifiers qpq ,
27332: ,QPL.BREAK_UOM_CONTEXT /* Proration */
27333: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
27334: FROM --[julin/5000789]
27335: (SELECT /*+ dynamic_sampling(1) */ *
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 ,
27419: ,QPL.BREAK_UOM_CONTEXT /* Proration */
27420: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
27421: FROM --[julin/5007789]
27422: (SELECT /*+ dynamic_sampling(1) */ *
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,
27550: ,QPL.BREAK_UOM_CONTEXT /* Proration */
27551: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
27552: FROM --[julin/5007789]
27553: (SELECT /*+ dynamic_sampling(1) */ *
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,
27656: ,QPL.BREAK_UOM_CONTEXT /* Proration */
27657: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
27658: FROM --[julin/5007789]
27659: (SELECT /*+ dynamic_sampling(1) */ *
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 ,
29456: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_line_index_cur,- No Index Used -,NA,NA
29457: */
29458: CURSOR l_get_line_index_cur IS
29459: SELECT line_index
29460: FROM qp_npreq_lines_tmp
29461: WHERE line_type_code <> G_ORDER_LEVEL;
29462:
29463: /*
29464: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_check_modlist_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
29489:
29490: -- find line_index(s) whose passed-in price list has not been qualified
29491: CURSOR l_check_no_modlist_cur IS
29492: SELECT /*+ index(qpll QP_PREQ_LINES_TMP_N2) */ qpll.line_index
29493: FROM qp_npreq_lines_tmp qpll
29494: WHERE qpll.line_type_code <> G_ORDER_LEVEL
29495: AND NOT EXISTS (
29496: SELECT /*+ ORDERED index(qpld QP_PREQ_LDETS_TMP_N1) index(qplat qp_preq_line_attrs_tmp_n2) l_check_modlist_cur */ 'X'
29497: FROM qp_npreq_ldets_tmp qpld,
29574:
29575: BEGIN
29576:
29577: --po integration start
29578: UPDATE qp_npreq_lines_tmp a
29579: SET pricing_status_code = G_STATUS_UPDATED
29580: Where list_price_override_flag in ('Y', 'O') --po integration
29581: AND line_type_code <> G_ORDER_LEVEL;
29582: --po integration end
29670:
29671: -- julin [3805113]: bulk update
29672: IF (l_a_list_header_id_tbl.COUNT > 0) THEN
29673: FORALL I IN l_a_list_header_id_tbl.first..l_a_list_header_id_tbl.last
29674: UPDATE qp_npreq_lines_tmp
29675: SET price_list_header_id = l_a_list_header_id_tbl(I)
29676: WHERE line_index = l_a_line_index_tbl(I);
29677: END IF;
29678:
29827: qplt.ROUNDING_FACTOR,
29828: qplt.CATCHWEIGHT_QTY,
29829: qplt.ACTUAL_ORDER_QUANTITY,
29830: NVL(qplt.price_flag,G_YES)
29831: FROM qp_npreq_lines_tmp qplt
29832: WHERE qplt.PRICING_STATUS_CODE IN (G_STATUS_UNCHANGED, G_STATUS_UPDATED)
29833: AND (qplt.PRICE_FLAG IN (G_YES,G_PHASE)
29834: OR qplt.PROCESSED_CODE = G_BY_ENGINE)
29835: AND nvl(qplt.USAGE_PRICING_TYPE,G_REGULAR_USAGE_TYPE) NOT IN
29840: /*
29841: INDX,QP_PREQ_GRP.call_calculation_engine.l_request_line_detail_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
29842: INDX,QP_PREQ_GRP.call_calculation_engine.l_request_line_detail_cur,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
29843:
29844: INDX,QP_PREQ_GRP.call_calculation_engine.l_request_line_detail_cur,qp_npreq_lines_tmp_N2,LINE_TYPE_CODE,1
29845: */
29846: CURSOR l_request_line_detail_cur(L_LINE_INDEX PLS_INTEGER,
29847: p_price_flag VARCHAR2) IS
29848: -- hint added as temporary R12 fix for 5573442
29896: QPLT.GROUP_QUANTITY,
29897: QPLT.GROUP_AMOUNT,
29898: QPLT.LINE_QUANTITY, --2388011_new
29899: QPLT.SELECTED_VOLUME_ATTR -- 2388011_new
29900: FROM qp_npreq_lines_tmp QPLL,
29901: qp_npreq_ldets_tmp QPLT
29902: WHERE QPLL.LINE_TYPE_CODE = G_ORDER_LEVEL
29903: AND QPLT.LINE_INDEX = QPLL.LINE_INDEX
29904: AND QPLT.PRICING_STATUS_CODE = G_STATUS_NEW
29937: AND RELATIONSHIP_TYPE_CODE = G_SERVICE_LINE;
29938:
29939: -- Cursor for getting the unit price of the parent line ex: Oracle 8i line
29940: /*
29941: INDX,QP_PREQ_GRP.call_calculation_engine.l_get_price_parent_line_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
29942: */
29943: CURSOR l_get_price_parent_line_cur(l_line_index PLS_INTEGER) IS
29944: SELECT UNIT_PRICE,
29945: LINE_UNIT_PRICE, --fix bug 2776849, uom_service_pricing
29945: LINE_UNIT_PRICE, --fix bug 2776849, uom_service_pricing
29946: PRICED_QUANTITY,
29947: LINE_QUANTITY,
29948: LINE_UOM_CODE --fix bug 2776849, uom_service_pricing
29949: FROM qp_npreq_lines_tmp
29950: WHERE LINE_INDEX = l_line_index;
29951:
29952: /*
29953: INDX,lQP_PREQ_GRP.create_qualifier_from_list._check_modlist_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
30676: l_message := ' No refernce of parent item or parent price is null for percent-based pricing.';
30677: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
30678: engine_debug ('line_index: '||i.line_index);
30679: END IF;
30680: UPDATE qp_npreq_lines_tmp qplines
30681: SET
30682: PROCESSED_FLAG = G_PROCESSED,
30683: PERCENT_PRICE = p_cal_req_line_rec.PERCENT_PRICE -- still keep the percent_price
30684: WHERE qplines.LINE_INDEX = i.LINE_INDEX;
30786: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
30787: engine_debug ('In Line Update Information');
30788: END IF;
30789: FORALL M IN G_LINE_INDEX_TBL_C.FIRST.. G_LINE_INDEX_TBL_C.LAST
30790: UPDATE qp_npreq_lines_tmp qplines
30791: SET PRICING_STATUS_CODE = G_STATUS_UPDATED,
30792: PROCESSED_FLAG = G_PROCESSED,
30793: UNIT_PRICE = G_UNIT_PRICE_TBL_C(M),
30794: LINE_UNIT_PRICE = G_LINE_UNIT_PRICE_TBL_C(M), --shu_latest
30864: WHERE LINE_DETAIL_INDEX = G_LINE_DETAIL_INDEX_TBL_R(O);
30865: END IF;
30866:
30867: -- Update all GSA Violations at 1 shot
30868: --INDX,QP_PREQ_GRP.call_calculation_engine.call_calculation_engine_upd6,qp_npreq_lines_tmp_N1,LINE_INDEX,1
30869: IF (G_GSA_LINE_INDEX_TBL_C.COUNT > 0) THEN
30870: FORALL K IN G_GSA_LINE_INDEX_TBL_C.FIRST .. G_GSA_LINE_INDEX_TBL_C.LAST
30871: UPDATE qp_npreq_lines_tmp
30872: SET PRICING_STATUS_CODE = G_STATUS_GSA_VIOLATION,
30867: -- Update all GSA Violations at 1 shot
30868: --INDX,QP_PREQ_GRP.call_calculation_engine.call_calculation_engine_upd6,qp_npreq_lines_tmp_N1,LINE_INDEX,1
30869: IF (G_GSA_LINE_INDEX_TBL_C.COUNT > 0) THEN
30870: FORALL K IN G_GSA_LINE_INDEX_TBL_C.FIRST .. G_GSA_LINE_INDEX_TBL_C.LAST
30871: UPDATE qp_npreq_lines_tmp
30872: SET PRICING_STATUS_CODE = G_STATUS_GSA_VIOLATION,
30873: PRICING_STATUS_TEXT = l_status_text
30874: WHERE LINE_INDEX = G_GSA_LINE_INDEX_TBL_C(K);
30875: END IF;
30933: INDX,QP_PREQ_GRP.formula_processing.l_get_formula_id_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
30934: INDX,QP_PREQ_GRP.formula_processing.l_get_formula_id_cur,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
30935: INDX,QP_PREQ_GRP.formula_processing.l_get_formula_id_cur,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
30936:
30937: -- Full table scan on qp_npreq_lines_tmp
30938: */
30939: CURSOR l_get_formula_id_cur IS
30940:
30941: SELECT /*+ ORDERED USE_NL(b) index(b QP_PREQ_LDETS_TMP_N1) l_get_formula_id_cur */
30942: b.LINE_INDEX , b.PRICE_FORMULA_ID,a.PRICING_EFFECTIVE_DATE,b.CREATED_FROM_LIST_LINE_ID,
30943: b.OPERAND_CALCULATION_CODE , b.OPERAND_VALUE, b.CREATED_FROM_LIST_LINE_TYPE,b.LINE_DETAIL_TYPE_CODE,
30944: b.GROUP_QUANTITY,b.LINE_QUANTITY GROUP_AMOUNT
30945: FROM
30946: qp_npreq_lines_tmp a,
30947: qp_npreq_ldets_tmp b
30948: WHERE a.LINE_INDEX = b.LINE_INDEX
30949: AND b.PRICING_STATUS_CODE = G_STATUS_NEW
30950: AND b.PRICING_PHASE_ID = p_pricing_phase_id
30951: AND b.PRICE_FORMULA_ID IS NOT NULL;
30952:
30953: CURSOR l_get_list_price_cur(p_line_index PLS_INTEGER) IS
30954: SELECT UNIT_PRICE
30955: FROM qp_npreq_lines_tmp
30956: WHERE LINE_INDEX = p_line_index;
30957:
30958: l_req_line_attrs_rec QP_FORMULA_PRICE_CALC_PVT.req_line_attrs_rec;
30959: l_req_line_attrs_tbl QP_FORMULA_PRICE_CALC_PVT.req_line_attrs_tbl;
31484: AND LINE_INDEX = i.line_index
31485: AND PRICING_STATUS_CODE = G_STATUS_NEW;
31486:
31487: /*
31488: INDX,QP_PREQ_GRP.formula_processing.formula_processing_upd2.qp_npreq_lines_tmp_N1,LINE_INDEX,1
31489: */
31490: UPDATE qp_npreq_lines_tmp --upd2
31491: SET UNIT_PRICE = l_formula_based_price,
31492: PRICING_STATUS_CODE = G_STATUS_UPDATED,
31486:
31487: /*
31488: INDX,QP_PREQ_GRP.formula_processing.formula_processing_upd2.qp_npreq_lines_tmp_N1,LINE_INDEX,1
31489: */
31490: UPDATE qp_npreq_lines_tmp --upd2
31491: SET UNIT_PRICE = l_formula_based_price,
31492: PRICING_STATUS_CODE = G_STATUS_UPDATED,
31493: PRICING_STATUS_TEXT ='Priced by formula'
31494: WHERE LINE_INDEX = i.line_index;
31505: AND LINE_INDEX = i.line_index
31506: AND PRICING_STATUS_CODE = G_STATUS_NEW;
31507:
31508: /*
31509: INDX,QP_PREQ_GRP.formula_processing.formula_processing_upd4.qp_npreq_lines_tmp_N1,LINE_INDEX,1
31510: */
31511: UPDATE qp_npreq_lines_tmp --upd4
31512: SET PERCENT_PRICE = l_formula_based_price,
31513: PRICING_STATUS_CODE = G_STATUS_UPDATED,
31507:
31508: /*
31509: INDX,QP_PREQ_GRP.formula_processing.formula_processing_upd4.qp_npreq_lines_tmp_N1,LINE_INDEX,1
31510: */
31511: UPDATE qp_npreq_lines_tmp --upd4
31512: SET PERCENT_PRICE = l_formula_based_price,
31513: PRICING_STATUS_CODE = G_STATUS_UPDATED,
31514: PRICING_STATUS_TEXT ='Priced by formula'
31515: WHERE LINE_INDEX = i.line_index;
31611: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
31612: ENGINE_DEBUG('Truncate_Temp_tables: Java Engine not Installed ----------');
31613: END IF;
31614: --added by yangli for Java Engine project
31615: --Delete from qp_npreq_lines_tmp;
31616: --Delete from qp_npreq_line_attrs_tmp;
31617: --Delete From qp_npreq_ldets_tmp;
31618: --Delete From qp_npreq_rltd_lines_tmp;
31619: --Delete From QP_PREQ_QUAL_TMP;
31621: --Delete From QP_PREQ_LINE_ATTRS_FORMULA_TMP;
31622:
31623:
31624: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
31625: select count(*) into l_count from qp_npreq_lines_tmp;
31626: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
31627: engine_debug('qp_npreq_lines_tmp '||l_count);
31628: END IF;
31629: select count(*) into l_count from qp_npreq_line_attrs_tmp;
31623:
31624: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
31625: select count(*) into l_count from qp_npreq_lines_tmp;
31626: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
31627: engine_debug('qp_npreq_lines_tmp '||l_count);
31628: END IF;
31629: select count(*) into l_count from qp_npreq_line_attrs_tmp;
31630: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
31631: engine_debug('qp_npreq_line_attrs_tmp '||l_count);
31694: INDX,QP_PREQ_GRP.set_order_level_rltd.l_line_index_cur,- No Index Used -,NA,NA
31695: */
31696: CURSOR l_line_index_cur(p_freeze_override_flag VARCHAR2) is
31697: SELECT line_index
31698: FROM qp_npreq_lines_tmp
31699: WHERE pricing_status_code = G_STATUS_UPDATED
31700: AND (PRICE_FLAG = G_YES
31701: OR
31702: (PRICE_FLAG = G_PHASE
32194: WHERE PRICING_PHASE_ID = p_pricing_phase_id
32195: AND PRICING_STATUS_CODE = G_STATUS_NEW;
32196:
32197: /*
32198: INDX,QP_PREQ_GRP.price_request.l_get_list_price_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
32199: */
32200: CURSOR l_get_list_price_cur(p_line_index PLS_INTEGER) IS
32201: SELECT UNIT_PRICE,LINE_QUANTITY,PRICED_QUANTITY,PRICED_UOM_CODE,LINE_UOM_CODE,PRICING_EFFECTIVE_DATE
32202: FROM qp_npreq_lines_tmp
32198: INDX,QP_PREQ_GRP.price_request.l_get_list_price_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
32199: */
32200: CURSOR l_get_list_price_cur(p_line_index PLS_INTEGER) IS
32201: SELECT UNIT_PRICE,LINE_QUANTITY,PRICED_QUANTITY,PRICED_UOM_CODE,LINE_UOM_CODE,PRICING_EFFECTIVE_DATE
32202: FROM qp_npreq_lines_tmp
32203: WHERE LINE_INDEX = p_line_index;
32204: --FOR UPDATE OF ROUNDING_FACTOR;
32205:
32206: /*
32214: --From QP_PRICE_REQ_SOURCES
32215: From QP_PRICE_REQ_SOURCES_V /* yangli */
32216: Where REQUEST_TYPE_CODE
32217: In (SELECT REQUEST_TYPE_CODE
32218: FROM qp_npreq_lines_tmp));
32219:
32220: CURSOR get_request_type_code_cur IS -- shu, aso rounding
32221: SELECT REQUEST_TYPE_CODE
32222: FROM qp_npreq_lines_tmp
32218: FROM qp_npreq_lines_tmp));
32219:
32220: CURSOR get_request_type_code_cur IS -- shu, aso rounding
32221: SELECT REQUEST_TYPE_CODE
32222: FROM qp_npreq_lines_tmp
32223: WHERE rownum=1;
32224:
32225: /*
32226: INDX,QP_PREQ_GRP.price_request.debug_cur,- No Index Used -,NA,NA
32226: INDX,QP_PREQ_GRP.price_request.debug_cur,- No Index Used -,NA,NA
32227: */
32228: CURSOR debug_cur IS
32229: SELECT REQUEST_TYPE_CODE,LINE_INDEX,LINE_TYPE_CODE,PRICE_FLAG,PRICE_LIST_HEADER_ID,ROUNDING_FACTOR
32230: FROM qp_npreq_lines_tmp;
32231:
32232: /*
32233: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
32234: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
32349: */
32350: CURSOR order_amount_cur IS
32351: --SELECT nvl(SUM(UNIT_PRICE * PRICED_QUANTITY),0)
32352: select nvl(sum(nvl(line_unit_price * line_quantity, UNIT_PRICE * PRICED_QUANTITY)),0)
32353: FROM qp_npreq_lines_tmp
32354: WHERE charge_periodicity_code is NULL; -- added for recurring charges Bug # 4465168
32355:
32356: /*
32357: INDX,QP_PREQ_GRP.price_request.limit_trans_cur,QP_LIMIT_TRANSACTIONS_U1,PRICE_REQUEST_CODE,1
32406: --added by yangli for Java Engine changes
32407: CURSOR l_lines_info_cur
32408: is
32409: select line_index, unit_price, price_flag
32410: from qp_npreq_lines_tmp
32411: where QP_JAVA_ENGINE_UTIL_PUB.Java_Engine_Running = 'N'
32412: UNION ALL
32413: select line_index, unit_price, price_flag
32414: from qp_int_lines
32619: END IF;
32620: END IF; -- end if (p_line_tbl.COUNT > 0)
32621:
32622: ELSE --G_TEMP_TABLE_INSERT_FLAG = 'N', direct insert/performance path, shulin dbg 2437534
32623: --FOR cl IN (select line_index, unit_price, price_flag from qp_npreq_lines_tmp)
32624: FOR cl IN l_lines_info_cur --by yangli for Java Engine changes
32625: LOOP
32626: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
32627: engine_debug('In direct insert Loop / performance path');
32808: l_price_flag_yes_only := NULL; -- reset, default
32809: -- Check if atleast there is one line with price_flag = 'Y'
32810: -- shu, begin bug 2437534 fix
32811: IF l_price_flag_yes_only is null THEN
32812: --FOR cl IN (select unit_price, price_flag from qp_npreq_lines_tmp)
32813: FOR cl IN l_lines_info_cur
32814: LOOP
32815: IF cl.price_flag = G_YES OR (cl.unit_price is NULL and cl.price_flag = G_PHASE) then
32816: l_price_flag_yes_only := G_YES;
32826: BEGIN
32827: IF QP_JAVA_ENGINE_UTIL_PUB.Java_Engine_Running = 'N' THEN
32828: SELECT 1
32829: INTO l_price_flag_indx
32830: FROM qp_npreq_lines_tmp
32831: WHERE PRICE_FLAG IN (G_YES,G_PHASE)
32832: AND ROWNUM=1;
32833: --added by yangli for Java Engine
32834: ELSE
32902: -- because Populate_Temp_Tables was not called
32903: BEGIN
32904: select line_id
32905: into G_ORDER_ID
32906: from qp_npreq_lines_tmp
32907: where line_type_code = QP_PREQ_GRP.G_ORDER_LEVEL;
32908: EXCEPTION
32909: WHEN NO_DATA_FOUND THEN
32910: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
33290: -- 3773652
33291: -- re-run Evaluate_Between after UOM conversion to check PLL really qualifies
33292: begin
33293: select 'x' into l_dummy
33294: from qp_npreq_lines_tmp
33295: where LINE_UOM_CODE <> nvl(PRICED_UOM_CODE, LINE_UOM_CODE)
33296: and (PRICE_FLAG = G_YES
33297: or
33298: PRICE_FLAG = G_PHASE and l_freeze_override_flag = G_YES)
33428:
33429: IF (G_PRICE_PHASE_FLAG=FALSE) THEN
33430:
33431: --[julin/pricebook] added hint
33432: UPDATE qp_npreq_lines_tmp a
33433: SET (price_list_header_id,validated_flag) =
33434: (SELECT /*+ index(b QP_PREQ_LINE_ATTRS_TMP_N2) */ distinct qp_number.canonical_to_number(value_from),validated_flag
33435: FROM qp_npreq_line_attrs_tmp b
33436: WHERE a.line_index = b.line_index
33804: engine_debug ('Total order amount = ' || v_order_amount);
33805: END IF;
33806:
33807: /*
33808: INDX,QP_PREQ_GRP.price_request.price_request_upd1,qp_npreq_lines_tmp_N2,LINE_TYPE_CODE,1
33809: */
33810: UPDATE qp_npreq_lines_tmp
33811: SET UNIT_PRICE = v_order_amount,
33812: PRICED_QUANTITY = 1
33806:
33807: /*
33808: INDX,QP_PREQ_GRP.price_request.price_request_upd1,qp_npreq_lines_tmp_N2,LINE_TYPE_CODE,1
33809: */
33810: UPDATE qp_npreq_lines_tmp
33811: SET UNIT_PRICE = v_order_amount,
33812: PRICED_QUANTITY = 1
33813: WHERE LINE_TYPE_CODE = 'ORDER';
33814: