[Home] [Help]
435:
436: -- shu new rounding implementation
437: /*+--------------------------------------------------------------------
438: | UPDATE_ROUNDING_FACTOR
439: | to update rounding_factor in the qp_npreq_lines_tmp table
440: +--------------------------------------------------------------------
441: */
442: PROCEDURE UPDATE_ROUNDING_FACTOR(
443: p_mcurr_installed_used_flag IN VARCHAR2,
447: l_routine VARCHAR2(240):= 'Routine:QP_PREQ_GRP.UPDATE_ROUNDING_FACTOR';
448:
449: CURSOR get_price_lists_cur IS
450: SELECT distinct price_list_header_id , currency_code , trunc(pricing_effective_date) pricing_effective_date
451: FROM qp_npreq_lines_tmp
452: where price_flag in ('Y','P')
453: and line_type_code = 'LINE'
454: and rounding_factor is null
455: and price_list_header_id > 0; --[julin/5395256]
508:
509: END LOOP;
510:
511: FORALL i in p_price_list_id_tbl.FIRST .. p_price_list_id_tbl.LAST
512: UPDATE qp_npreq_lines_tmp
513: SET ROUNDING_FACTOR = p_rounding_factor_tbl(i)
514: WHERE PRICE_FLAG in (QP_PREQ_GRP.G_YES, QP_PREQ_GRP.G_PHASE)
515: AND PRICE_LIST_HEADER_ID = p_price_list_id_tbl(i)
516: AND CURRENCY_CODE = p_currency_code_tbl(i)
3390:
3391: BEGIN
3392: x_status_code := FND_API.G_RET_STS_SUCCESS;
3393: /*
3394: INDX,QP_PREQ_GRP.Update_Processing_Order.Update_Processing_Order_upd1,qp_npreq_lines_tmp_U1,LINE_INDEX,1
3395: */
3396:
3397: -- julin [3805113]: using bulk collect and update
3398: OPEN l_Service_Cur;
3404: EXIT WHEN l_line_index_tbl.count = 0;
3405:
3406: -- julin [3805113]: bulk update
3407: FORALL I IN l_line_index_tbl.first..l_line_index_tbl.last
3408: UPDATE qp_npreq_lines_tmp --upd1
3409: SET PROCESSING_ORDER = 1
3410: WHERE LINE_INDEX = l_line_index_tbl(I);
3411:
3412: --Assuming that parent line has it index on related_line_index column
3410: WHERE LINE_INDEX = l_line_index_tbl(I);
3411:
3412: --Assuming that parent line has it index on related_line_index column
3413: /*
3414: INDX,QP_PREQ_GRP.Update_Processing_Order.Update_Processing_Order_upd2,qp_npreq_lines_tmp_U1,LINE_INDEX,1
3415: */
3416: -- julin [3805113]: bulk update
3417: FORALL I IN l_related_line_index_tbl.first..l_related_line_index_tbl.last
3418: UPDATE qp_npreq_lines_tmp --upd2
3414: INDX,QP_PREQ_GRP.Update_Processing_Order.Update_Processing_Order_upd2,qp_npreq_lines_tmp_U1,LINE_INDEX,1
3415: */
3416: -- julin [3805113]: bulk update
3417: FORALL I IN l_related_line_index_tbl.first..l_related_line_index_tbl.last
3418: UPDATE qp_npreq_lines_tmp --upd2
3419: SET PROCESSING_ORDER = 2
3420: WHERE LINE_INDEX = l_related_line_index_tbl(I);
3421:
3422: END LOOP;
3424:
3425: --added for 4900095
3426: --marking the service lines with uom_quantity as null
3427: --this will be used in lumpsum calculation
3428: for cl in (select line_index from qp_npreq_lines_tmp line
3429: where processing_order = 2
3430: and (contract_start_date is not null or contract_end_date is not null)
3431: and uom_quantity is null)
3432: loop
3474: l_routine VARCHAR2(240):='QP_PREQ_GRP.SET_STATUS_CODE';
3475: BEGIN
3476: IF(p_temp_table = 'LINES') THEN
3477: /*
3478: INDX,QP_PREQ_GRP.set_status_code.set_status_code_upd1,qp_npreq_lines_tmp_N1,LINE_INDEX,1
3479: */
3480: -- shulin fix bug1745788, widen substr to 2000
3481: UPDATE qp_npreq_lines_tmp --upd1
3482: SET PRICING_STATUS_CODE = p_status_code,
3477: /*
3478: INDX,QP_PREQ_GRP.set_status_code.set_status_code_upd1,qp_npreq_lines_tmp_N1,LINE_INDEX,1
3479: */
3480: -- shulin fix bug1745788, widen substr to 2000
3481: UPDATE qp_npreq_lines_tmp --upd1
3482: SET PRICING_STATUS_CODE = p_status_code,
3483: PRICING_STATUS_TEXT = substr(p_status_text,1,2000)
3484: WHERE LINE_INDEX = p_line_index;
3485: ELSIF (p_temp_table = 'LDETS' AND p_line_index IS NOT NULL) THEN
3535: l_routine VARCHAR2(240):='QP_PREQ_GRP.SET_PROCESSED_CODE';
3536: BEGIN
3537: IF(p_temp_table = 'LINES') THEN
3538: /*
3539: INDX,QP_PREQ_GRP.set_processed_code.set_processed_code_upd1,qp_npreq_lines_tmp_N1,LINE_INDEX,1
3540: */
3541: UPDATE qp_npreq_lines_tmp --upd1
3542: SET PROCESSED_CODE = p_processed_code
3543: WHERE LINE_INDEX = p_line_index;
3537: IF(p_temp_table = 'LINES') THEN
3538: /*
3539: INDX,QP_PREQ_GRP.set_processed_code.set_processed_code_upd1,qp_npreq_lines_tmp_N1,LINE_INDEX,1
3540: */
3541: UPDATE qp_npreq_lines_tmp --upd1
3542: SET PROCESSED_CODE = p_processed_code
3543: WHERE LINE_INDEX = p_line_index;
3544: ELSIF (p_temp_table = 'ATTRS' AND p_line_index IS NOT NULL
3545: AND p_line_detail_index IS NULL) THEN
3567: |PROCEDURE DELETE_ALL_LINES
3568: |This procedure mark qp_npreq_line_attrs_tmp and qp_npreq_ldets_tmp
3569: |for delete. And mark qp_preq_lines status code as
3570: |whatever calling procedure wanted. Which is usually
3571: |not a 'Delete' because record in qp_npreq_lines_tmp
3572: |is the only one which communicates with outside
3573: |world (order managment for example).
3574: +----------------------------------------------------
3575: */
4407: AS
4408: l_routine VARCHAR2(240):='Routine:QP_PREQ_GRP.INSERT_LINES';
4409: BEGIN
4410: x_status_code := FND_API.G_RET_STS_SUCCESS;
4411: INSERT INTO qp_npreq_lines_tmp
4412: (LINE_INDEX,
4413: LINE_TYPE_CODE,
4414: PRICING_EFFECTIVE_DATE,
4415: START_DATE_ACTIVE_FIRST,
4724:
4725: x_status_code := FND_API.G_RET_STS_SUCCESS;
4726:
4727: FORALL i in p_line_index.FIRST..p_line_index.LAST
4728: INSERT INTO qp_npreq_lines_tmp
4729: (LINE_INDEX,
4730: LINE_TYPE_CODE,
4731: PRICING_EFFECTIVE_DATE,
4732: START_DATE_ACTIVE_FIRST,
5269: --added by yangli for Java Engine project
5270:
5271: FORALL i in p_line_index.FIRST..p_line_index.LAST
5272:
5273: INSERT INTO qp_npreq_lines_tmp
5274: (LINE_INDEX,
5275: LINE_TYPE_CODE,
5276: PRICING_EFFECTIVE_DATE,
5277: START_DATE_ACTIVE_FIRST,
7564: LINE_TYPE_CODE,
7565: LINE_UOM_CODE,
7566: PRICED_UOM_CODE,
7567: PRICE_FLAG
7568: FROM qp_npreq_lines_tmp;
7569: --WHERE LINE_UOM_CODE <> PRICED_UOM_CODE;
7570: /*WHERE (PRICE_FLAG = G_YES
7571: OR
7572: (PRICE_FLAG = G_PHASE
7595: */
7596: CURSOR order_amount_cur IS
7597: --SELECT nvl(SUM(UNIT_PRICE * PRICED_QUANTITY),0)
7598: select nvl(sum(nvl(line_unit_price * line_quantity, UNIT_PRICE * PRICED_QUANTITY)),0) /* 3143535 */
7599: FROM qp_npreq_lines_tmp
7600: WHERE charge_periodicity_code is NULL; -- added for recurring charges Bug # 4465168
7601:
7602:
7603:
7685: END IF;
7686: END IF;
7687: -- Reset the unit price if price event occurs
7688: IF (i.PRICE_FLAG = G_YES OR (i.PRICE_FLAG = G_PHASE AND p_freeze_override_flag = G_YES)) THEN
7689: UPDATE qp_npreq_lines_tmp
7690: SET UNIT_PRICE = NULL
7691: WHERE LINE_INDEX = I.LINE_INDEX;
7692: END IF;
7693:
7855: x_status_text =>l_status_text);
7856: END IF; */ -- Attribute Mapping is sourcing Volume Attributes 01/14/02, AC Nielsen Performance
7857:
7858:
7859: /*UPDATE qp_npreq_lines_tmp
7860: SET LINE_AMOUNT = nvl(nvl(I.PRICED_QUANTITY,I.LINE_QUANTITY) * I.UNIT_PRICE,0)
7861: WHERE LINE_INDEX = I.LINE_INDEX; */
7862:
7863: G_QUALIFIER_TEMP := 'N';
7941: G_NO,
7942: G_NO,
7943: G_STATUS_UNCHANGED,
7944: G_NO
7945: FROM qp_npreq_lines_tmp;
7946:
7947: END IF;
7948:
7949: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
9340: l_pricing_effective_date Date;
9341: l_debug varchar2(3);
9342:
9343: /*
9344: INDX,QP_PREQ_GRP.set_expiration_dates.l_get_pricing_effective_date,qp_npreq_lines_tmp_N1,LINE_INDEX,1
9345: */
9346: CURSOR l_get_pricing_effective_date (p_line_index IN PLS_INTEGER) IS
9347: SELECT pricing_effective_date
9348: FROM qp_npreq_lines_tmp
9344: INDX,QP_PREQ_GRP.set_expiration_dates.l_get_pricing_effective_date,qp_npreq_lines_tmp_N1,LINE_INDEX,1
9345: */
9346: CURSOR l_get_pricing_effective_date (p_line_index IN PLS_INTEGER) IS
9347: SELECT pricing_effective_date
9348: FROM qp_npreq_lines_tmp
9349: WHERE line_index = p_line_index;
9350:
9351: --added by yangli for Java Engine interface tables
9352: /*
9459: EXTENDED_PRICE /* block pricing */
9460: --EXTENDED_SELLING_PRICE_UR, --[prarasto:Post Round]New fields for unrounded values, [julin/postround] redesign
9461: --ORDER_UOM_SELLING_PRICE_UR, --[prarasto:Post Round]
9462: --ADJUSTED_UNIT_PRICE_UR --[prarasto:Post Round]
9463: FROM qp_npreq_lines_tmp;
9464:
9465: /*
9466: INDX,QP_PREQ_GRP.populate_output.l_qual_cur,qp_npreq_ldets_tmp_N4,PRICING_STATUS_CODE,1
9467:
10546: |across all lines that have same product context,attribute,value
10547: |, pricing context,attribute and have list_line which is
10548: |line group. The output on the sum will be populated
10549: |either on GROUP_QUANTITY or qualifier_amt column
10550: |of qp_npreq_lines_tmp.
10551: +--------------------------------------------------------
10552: */
10553:
10554: PROCEDURE PROCESS_LINE_GROUP(p_pricing_phase_id NUMBER,
10594:
10595: --begin 2388011, grp_pbh_amt
10596: CURSOR l_get_list_price_cur(p_list_line_id NUMBER) IS
10597: SELECT a.LINE_INDEX ,a.UNIT_PRICE
10598: FROM qp_npreq_lines_tmp a,qp_npreq_ldets_tmp b
10599: WHERE a.LINE_INDEX = b.LINE_INDEX
10600: AND b.CREATED_FROM_LIST_LINE_ID = p_list_line_id;
10601: --end 2388011, grp_pbh_amt
10602:
10799: engine_debug('p_line_index : '||p_line_index);
10800: engine_debug('p_product_uom_code: '||nvl(p_product_uom_code,'Null'));
10801: END IF;
10802: /*
10803: INDX,QP_PREQ_GRP.is_uom_match.is_uom_match_sel1,qp_npreq_lines_tmp_N1,LINE_INDEX,1
10804: */
10805: SELECT 'X'
10806: INTO l_dummy
10807: FROM qp_npreq_lines_tmp qplines
10803: INDX,QP_PREQ_GRP.is_uom_match.is_uom_match_sel1,qp_npreq_lines_tmp_N1,LINE_INDEX,1
10804: */
10805: SELECT 'X'
10806: INTO l_dummy
10807: FROM qp_npreq_lines_tmp qplines
10808: WHERE qplines.LINE_INDEX = p_line_index
10809: AND nvl(qplines.PRICED_UOM_CODE,qplines.LINE_UOM_CODE) =
10810: nvl(p_product_uom_code,nvl(qplines.PRICED_UOM_CODE,qplines.LINE_UOM_CODE));
10811:
10891: engine_debug('In Update Request Line Status : ' || p_status_code);
10892: END IF;
10893:
10894: /* --po integration start
10895: UPDATE qp_npreq_lines_tmp a
10896: SET pricing_status_code = G_STATUS_UPDATED
10897: Where list_price_override_flag in ('Y', 'O') --po integration
10898: AND line_type_code <> G_ORDER_LEVEL;
10899: --po integration end
10909: INDX,QP_PREQ_GRP.update_request_line_status.sel1,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
10910: INDX,QP_PREQ_GRP.update_request_line_status.sel1,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
10911: */
10912:
10913: UPDATE qp_npreq_lines_tmp a
10914: SET pricing_status_code = G_STATUS_UPDATED
10915: ,processed_code = NULL -- bug 3501150
10916: WHERE EXISTS (SELECT 'x'
10917: from qp_npreq_ldets_tmp b
10920: AND b.pricing_phase_id = G_PRICE_LIST_PHASE_ID)
10921: AND line_type_code <> G_ORDER_LEVEL;
10922:
10923: -- To make sure lines which did not get the price and do not have a record in qp_npreq_ldets_tmp get picked up
10924: UPDATE qp_npreq_lines_tmp a
10925: SET processed_code = G_STS_LHS_NOT_FOUND
10926: WHERE NOT EXISTS (SELECT 'x'
10927: FROM qp_npreq_ldets_tmp b
10928: WHERE a.line_index = b.line_index
10941:
10942: DECLARE
10943:
10944: CURSOR tmp_data IS
10945: SELECT rounding_factor FROM qp_npreq_lines_tmp;
10946: BEGIN
10947:
10948: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
10949: engine_debug ('G_MCURR_PROFILE: '||G_MULTI_CURRENCY_PROFILE);
10955: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
10956: engine_debug ('get rounding_factor from qp_currency_details table...');
10957: END IF;
10958:
10959: UPDATE qp_npreq_lines_tmp a
10960: SET ROUNDING_FACTOR = (SELECT qcdt.selling_rounding_factor
10961: FROM qp_currency_details qcdt, qp_list_headers_b qhdr
10962: WHERE qhdr.list_header_id = a.price_list_header_id
10963: AND qhdr.currency_header_id = qcdt.currency_header_id
10972:
10973: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
10974: engine_debug('get rounding factor from qp_list_headers_b...');
10975: END IF;
10976: UPDATE qp_npreq_lines_tmp a
10977: SET ROUNDING_FACTOR = (SELECT qhdr.rounding_factor
10978: FROM qp_list_headers_b qhdr
10979: WHERE qhdr.list_header_id = a.price_list_header_id)
10980: WHERE ROUNDING_FACTOR IS NULL;
11008: INDX,QP_PREQ_GRP.update_request_line_status.sel3,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
11009: INDX,QP_PREQ_GRP.update_request_line_status.sel3,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
11010: */
11011:
11012: UPDATE qp_npreq_lines_tmp a
11013: SET processed_code = G_STS_LHS_NOT_FOUND
11014: WHERE NOT EXISTS (SELECT 'x'
11015: FROM qp_npreq_ldets_tmp b
11016: WHERE a.line_index = b.line_index
11197: IS
11198: SELECT /*+ ORDERED USE_NL(a) l_line_amount_cur */
11199: fnd_number.canonical_to_number(c.value_from) group_value,
11200: a.priced_quantity
11201: FROM qp_npreq_line_attrs_tmp c, qp_npreq_lines_tmp a
11202: WHERE a.line_index = p_line_index
11203: AND a.LINE_INDEX = c.LINE_INDEX
11204: AND c.line_detail_index = p_line_detail_index
11205: AND c.context = p_context
11420:
11421: -- begin shu, fix group of lines lumpsum based on ITEM_AMOUNT
11422:
11423: SELECT UNIT_PRICE INTO l_list_price
11424: FROM qp_npreq_lines_tmp
11425: WHERE LINE_INDEX = i.line_index;
11426:
11427: /* Handled l_list_price = 0 bug2385874 */
11428: IF (l_list_price IS NOT NULL and l_list_price <>0) THEN -- avoid division by zero
11588: -- [julin/5085310]
11589: IF (i.attribute = G_LINE_AMT_ATTRIBUTE) THEN
11590:
11591: SELECT UNIT_PRICE INTO l_list_price
11592: FROM qp_npreq_lines_tmp
11593: WHERE LINE_INDEX = i.line_index;
11594:
11595: /* Handled l_list_price = 0 bug2385874 */
11596: IF (l_list_price IS NOT NULL and l_list_price <>0) THEN -- avoid division by zero
11705: INDX,QP_PREQ_GRP.handle_break.l_price_break_detail_cur,qp_npreq_line_attrs_tmp_N2,CONTEXT,3
11706: INDX,QP_PREQ_GRP.handle_break.l_price_break_detail_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE,4
11707: INDX,QP_PREQ_GRP.handle_break.l_price_break_detail_cur,qp_npreq_line_attrs_tmp_N2,LINE_INDEX,5
11708:
11709: INDX,QP_PREQ_GRP.handle_break.l_price_break_detail_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
11710:
11711: INDX,QP_PREQ_GRP.handle_break.l_price_break_detail_cur,QP_PRICING_ATTRIBUTES_N4,PRICING_ATTRIBUTE_CONTEXT,1
11712: INDX,QP_PREQ_GRP.handle_break.l_price_break_detail_cur,QP_PRICING_ATTRIBUTES_N4,PRICING_ATTRIBUTE,2
11713:
11766: qplines.contract_start_date, -- R12 partial period pricing
11767: qplines.contract_end_date -- R12 partial period pricing
11768: FROM
11769: qp_npreq_line_attrs_tmp qplat_pricing,
11770: qp_npreq_lines_tmp qplines,
11771: qp_rltd_modifiers qprm,
11772: qp_pricing_attributes qppa,
11773: qp_list_lines qpll
11774: ,qp_list_headers_b qph /* Vivek */
11899: qplines.contract_start_date, -- R12 partial period pricing
11900: qplines.contract_end_date -- R12 partial period pricing
11901: FROM
11902: qp_npreq_line_attrs_tmp qplat_pricing,
11903: qp_npreq_lines_tmp qplines,
11904: qp_rltd_modifiers qprm,
11905: qp_pricing_attributes qppa,
11906: qp_list_lines qpll
11907: WHERE qprm.from_rltd_modifier_id = p_list_line_id
12447: -- call the oks api only once
12448: if l_first_time_flag = 'Y' then
12449: select contract_start_date, contract_end_date
12450: into l_contract_start_date, l_contract_end_date
12451: from qp_npreq_lines_tmp
12452: where line_index = p_line_index;
12453:
12454: -- OKS_QP_INT.get_conversion_factor was replaced by
12455: -- OKS_OMINT_PUB.get_quantity in R12 for partial period pricing
13288: -- if price break price lists are deleted at this point , then mark the status on the line for
13289: -- secondary or big search. Do this only for pricing phase
13290: IF (g_failed_ld_tbl_g.count > 0 and p_pricing_phase_id = G_PRICE_LIST_PHASE_ID) THEN
13291: -- To make sure lines which did not get the price and do not have a record in qp_npreq_ldets_tmp get picked up
13292: UPDATE qp_npreq_lines_tmp a
13293: SET processed_code = G_STS_LHS_NOT_FOUND
13294: WHERE NOT EXISTS (SELECT 'x'
13295: FROM qp_npreq_ldets_tmp b
13296: WHERE a.line_index = b.line_index
14609: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel1,qp_npreq_line_attrs_tmp_N1,ATTRIBUTE_TYPE,2
14610: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel1,qp_npreq_line_attrs_tmp_N1,PRICING_STATUS_CODE,3
14611: */
14612: IF (G_PRICE_PHASE_FLAG = TRUE ) THEN -- Ravi
14613: UPDATE /*+ dynamic_sampling(1) */ qp_npreq_lines_tmp a
14614: SET processed_code = G_NO_LIST_PASSED,
14615: validated_flag = G_NO
14616: WHERE not exists (SELECT 'x'
14617: FROM qp_npreq_line_attrs_tmp b
14628: engine_debug ('after update1...');
14629: END IF;
14630:
14631: /*
14632: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_upd3,qp_npreq_lines_tmp_N3,VALIDATED_FLAG,1
14633: */
14634: /*
14635: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel2,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
14636: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel2,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
14642: IF (G_PRICE_PHASE_FLAG = TRUE ) THEN -- Ravi
14643:
14644: --[julin/pricebook] added hint
14645: --[julin/5223502] price_list_header_id directly passed in via lines_tmp has precedence over sourced value
14646: UPDATE /*+ dynamic_sampling(1) */ qp_npreq_lines_tmp a
14647: SET (price_list_header_id,validated_flag) =
14648: (SELECT /*+ index(b QP_PREQ_LINE_ATTRS_TMP_N2) */ distinct
14649: CASE WHEN (a.PRICE_LIST_HEADER_ID < 0) THEN qp_number.canonical_to_number(value_from)
14650: ELSE a.PRICE_LIST_HEADER_ID
14672: IF (G_PRICE_PHASE_FLAG = TRUE AND G_MANUAL_ADJUSTMENTS_CALL_FLAG = G_NO) THEN
14673: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
14674: engine_debug('Trying to Update Unit Price To Null');
14675: END IF;
14676: UPDATE qp_npreq_lines_tmp a
14677: SET unit_price = NULL
14678: WHERE (price_flag = G_YES
14679: OR
14680: (price_flag = G_PHASE
14688: engine_debug ('after update3');
14689: END IF;
14690:
14691: /*
14692: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_upd4,qp_npreq_lines_tmp_N3,VALIDATED_FLAG,1
14693: */
14694: /*
14695: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel3,QP_QUALIFIERS_N7,QUALIFIER_CONTEXT,1
14696: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel3,QP_QUALIFIERS_N7,QUALIFIER_ATTRIBUTE,2
14696: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel3,QP_QUALIFIERS_N7,QUALIFIER_ATTRIBUTE,2
14697: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel3,QP_QUALIFIERS_N7,LIST_HEADER_ID,3
14698: */
14699: IF (G_PRICE_PHASE_FLAG = TRUE) THEN -- Ravi,
14700: UPDATE qp_npreq_lines_tmp a
14701: SET qualifiers_exist_flag = G_YES
14702: WHERE price_list_header_id IS NOT NULL
14703: AND validated_flag = G_NO
14704: AND EXISTS (SELECT 'x'
14711: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel4,QP_PRICING_ATTRIBUTES_N6,LIST_HEADER_ID,1
14712: INDX,QP_PREQ_GRP.update_price_list_information.update_price_list_information_sel4,QP_PRICING_ATTRIBUTES_N6,QUALIFICATION_IND,2
14713: */
14714:
14715: UPDATE qp_npreq_lines_tmp a
14716: SET pricing_attrs_exist_flag = G_YES
14717: WHERE price_list_header_id IS NOT NULL
14718: AND EXISTS
14719: (SELECT 'x'
14759: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel1,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE,4
14760: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel1,qp_npreq_line_attrs_tmp_N2,LINE_INDEX,5
14761: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel1,qp_npreq_line_attrs_tmp_N2,VALUE_FROM,6
14762: */
14763: UPDATE qp_npreq_lines_tmp c
14764: SET primary_qualifiers_match_flag = 'Y'
14765: WHERE EXISTS(
14766: SELECT /*+ index(a QP_QUALIFIERS_N7) */ 'x' --bug#11924681
14767: FROM QP_QUALIFIERS a,
14792: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel2,qp_npreq_line_attrs_tmp_N2,CONTEXT,3
14793: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel2,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE,4
14794: INDX,QP_PREQ_GRP.update_sec_price_list_info.update_sec_price_list_info_sel2,qp_npreq_line_attrs_tmp_N2,LINE_INDEX,5
14795: */
14796: UPDATE qp_npreq_lines_tmp c
14797: SET primary_qualifiers_match_flag = 'Y'
14798: WHERE EXISTS(
14799: SELECT 'x'
14800: FROM qp_npreq_line_attrs_tmp b ,QP_QUALIFIERS a
14810: AND b.LINE_INDEX = c.LINE_INDEX
14811: AND c.VALIDATED_FLAG = G_NO)
14812: AND c.VALIDATED_FLAG = G_NO ;
14813:
14814: UPDATE qp_npreq_lines_tmp c
14815: SET primary_qualifiers_match_flag = 'Y'
14816: WHERE EXISTS(
14817: SELECT 'x'
14818: FROM qp_npreq_line_attrs_tmp b ,QP_QUALIFIERS a
14874: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_LIST_LINES_PK,LIST_LINE_ID,1
14875:
14876: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
14877:
14878: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
14879:
14880: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_LIST_HEADERS_B_PK,LINE_TYPE_CODE,2
14881:
14882: INDX,QP_PREQ_GRP.perform_secondary_search.l_sec_list_cur,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
14939: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
14940: ,NULL QUALIFIER_TYPE -- 3863226
14941: ,QPLAT_PROD.DERIVED_QUALIFIER_FLAG -- Added for TCA
14942: FROM
14943: qp_npreq_lines_tmp QPLINES,
14944: QP_QUALIFIERS QPQ,
14945: qp_npreq_line_attrs_tmp QPLAT_PROD,
14946: QP_PRICING_ATTRIBUTES QPA,
14947: QP_LIST_LINES QPL,
15060: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
15061: ,NULL QUALIFIER_TYPE -- 3863226
15062: ,QPLAT_PRIC.DERIVED_QUALIFIER_FLAG -- Added for TCA
15063: FROM
15064: qp_npreq_lines_tmp qplines,
15065: QP_QUALIFIERS qpq,
15066: qp_npreq_line_attrs_tmp qplat_prod,
15067: QP_PRICING_ATTRIBUTES qpa,
15068: qp_npreq_line_attrs_tmp qplat_pric,
15187: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
15188: ,DECODE(QPQ.list_line_id, -1, 'HQ', 'LQ') QUALIFIER_TYPE -- 3863226
15189: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
15190: FROM
15191: qp_npreq_lines_tmp QPLINES,
15192: QP_QUALIFIERS QPQ,
15193: QP_QUALIFIERS QPSQ,
15194: qp_npreq_line_attrs_tmp QPLAT_PROD,
15195: qp_npreq_line_attrs_tmp QPLAT_Q,
15328: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
15329: ,DECODE(QPQ.list_line_id, -1, 'HQ', 'LQ') QUALIFIER_TYPE -- 3863226
15330: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
15331: FROM
15332: qp_npreq_lines_tmp qplines,
15333: QP_QUALIFIERS qpq,
15334: QP_QUALIFIERS qpsq,
15335: qp_npreq_line_attrs_tmp qplat_prod,
15336: QP_PRICING_ATTRIBUTES qpa,
15486: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
15487: ,NULL QUALIFIER_TYPE -- 3863226
15488: ,QPLAT_PROD.DERIVED_QUALIFIER_FLAG -- Added for TCA
15489: FROM
15490: qp_npreq_lines_tmp QPLINES,
15491: QP_QUALIFIERS QPQ,
15492: qp_npreq_line_attrs_tmp QPLAT_PROD,
15493: QP_PRICING_ATTRIBUTES QPA,
15494: QP_LIST_LINES QPL,
15667: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
15668: ,NULL QUALIFIER_TYPE -- 3863226
15669: ,QPLAT_PRIC.DERIVED_QUALIFIER_FLAG -- Added for TCA
15670: FROM
15671: qp_npreq_lines_tmp qplines,
15672: QP_QUALIFIERS qpq,
15673: qp_npreq_line_attrs_tmp qplat_prod,
15674: QP_PRICING_ATTRIBUTES qpa,
15675: qp_npreq_line_attrs_tmp qplat_pric,
15854: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
15855: ,DECODE(QPQ.list_line_id, -1, 'HQ', 'LQ') QUALIFIER_TYPE --3863226
15856: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
15857: FROM
15858: qp_npreq_lines_tmp QPLINES,
15859: QP_QUALIFIERS QPQ,
15860: QP_QUALIFIERS QPSQ,
15861: qp_npreq_line_attrs_tmp QPLAT_PROD,
15862: qp_npreq_line_attrs_tmp QPLAT_Q,
16055: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
16056: ,DECODE(QPQ.list_line_id, -1, 'HQ', 'LQ') QUALIFIER_TYPE --3863226
16057: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
16058: FROM
16059: qp_npreq_lines_tmp qplines,
16060: QP_QUALIFIERS qpq,
16061: QP_QUALIFIERS qpsq,
16062: qp_npreq_line_attrs_tmp qplat_prod,
16063: QP_PRICING_ATTRIBUTES qpa,
17002: -- 3773652
17003: -- re-run Evaluate_Between after UOM conversion to check PLL really qualifies
17004: begin
17005: select 'x' into l_dummy
17006: from qp_npreq_lines_tmp
17007: where LINE_UOM_CODE <> nvl(PRICED_UOM_CODE, LINE_UOM_CODE)
17008: and (PRICE_FLAG = G_YES
17009: or
17010: PRICE_FLAG = G_PHASE and p_freeze_override_flag = G_YES)
17093: x_status_code OUT NOCOPY VARCHAR2,
17094: x_status_text OUT NOCOPY VARCHAR2)
17095: AS
17096: /*
17097: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,qp_npreq_lines_tmp_N3,VALIDATED_FLAG,1
17098: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,qp_npreq_lines_tmp_N3,QUALIFIERS_EXIST_FLAG,2
17099: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,qp_npreq_lines_tmp_N3,PRICING_ATTRS_EXIST_FLAG,3
17100:
17101: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,qp_npreq_line_attrs_tmp_N1,LINE_INDEX,1
17094: x_status_text OUT NOCOPY VARCHAR2)
17095: AS
17096: /*
17097: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,qp_npreq_lines_tmp_N3,VALIDATED_FLAG,1
17098: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,qp_npreq_lines_tmp_N3,QUALIFIERS_EXIST_FLAG,2
17099: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,qp_npreq_lines_tmp_N3,PRICING_ATTRS_EXIST_FLAG,3
17100:
17101: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,qp_npreq_line_attrs_tmp_N1,LINE_INDEX,1
17102: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,qp_npreq_line_attrs_tmp_N1,ATTRIBUTE_TYPE,2
17095: AS
17096: /*
17097: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,qp_npreq_lines_tmp_N3,VALIDATED_FLAG,1
17098: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,qp_npreq_lines_tmp_N3,QUALIFIERS_EXIST_FLAG,2
17099: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,qp_npreq_lines_tmp_N3,PRICING_ATTRS_EXIST_FLAG,3
17100:
17101: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,qp_npreq_line_attrs_tmp_N1,LINE_INDEX,1
17102: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,qp_npreq_line_attrs_tmp_N1,ATTRIBUTE_TYPE,2
17103: INDX,QP_PREQ_GRP.list_header_search.l_list_cur,qp_npreq_line_attrs_tmp_N1,PRICING_STATUS_CODE,3
17158: ,QPL.BREAK_UOM_CONTEXT /* Proration */
17159: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
17160: ,QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
17161: FROM
17162: qp_npreq_lines_tmp QPLINES,
17163: qp_npreq_line_attrs_tmp qplat_prod,
17164: QP_PRICING_ATTRIBUTES QPA,
17165: QP_LIST_LINES QPL,
17166: QP_LIST_HEADERS_B QPH,
17263: ,QPL.BREAK_UOM_CONTEXT /* Proration */
17264: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
17265: ,QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
17266: FROM
17267: qp_npreq_lines_tmp qplines,
17268: qp_npreq_line_attrs_tmp qplat_prod,
17269: QP_PRICING_ATTRIBUTES qpa,
17270: qp_npreq_line_attrs_tmp qplat_pric,
17271: QP_LIST_LINES qpl,
17389: ,QPL.BREAK_UOM_CONTEXT /* Proration */
17390: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
17391: ,QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
17392: FROM
17393: qp_npreq_lines_tmp QPLINES,
17394: qp_npreq_line_attrs_tmp qplat_prod,
17395: QP_PRICING_ATTRIBUTES QPA,
17396: QP_LIST_LINES QPL,
17397: QP_LIST_HEADERS_B QPH,
17546: ,QPL.BREAK_UOM_CONTEXT /* Proration */
17547: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
17548: ,QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
17549: FROM
17550: qp_npreq_lines_tmp qplines,
17551: qp_npreq_line_attrs_tmp qplat_prod,
17552: QP_PRICING_ATTRIBUTES qpa,
17553: qp_npreq_line_attrs_tmp qplat_pric,
17554: QP_LIST_LINES qpl,
17669: ORDER BY 1,2; --ORDER BY LINE_INDEX AND LIST LINE ID
17670:
17671:
17672: /*
17673: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_lines_tmp_N3,VALIDATED_FLAG,1
17674: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_lines_tmp_N3,QUALIFIERS_EXIST_FLAG,2
17675: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_lines_tmp_N3,PRICING_ATTRS_EXIST_FLAG,3
17676:
17677: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
17670:
17671:
17672: /*
17673: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_lines_tmp_N3,VALIDATED_FLAG,1
17674: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_lines_tmp_N3,QUALIFIERS_EXIST_FLAG,2
17675: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_lines_tmp_N3,PRICING_ATTRS_EXIST_FLAG,3
17676:
17677: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
17678: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
17671:
17672: /*
17673: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_lines_tmp_N3,VALIDATED_FLAG,1
17674: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_lines_tmp_N3,QUALIFIERS_EXIST_FLAG,2
17675: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,qp_npreq_lines_tmp_N3,PRICING_ATTRS_EXIST_FLAG,3
17676:
17677: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_QUALIFIERS_N1,LIST_HEADER_ID,1
17678: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_QUALIFIERS_N1,COMPARISON_OPERATOR_CODE,4
17679: INDX,QP_PREQ_GRP.list_header_search.l_list_qual_cur,QP_QUALIFIERS_N1,ACTIVE_FLAG,6
17762: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
17763: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
17764: ,QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
17765: FROM
17766: qp_npreq_lines_tmp QPLINES,
17767: QP_QUALIFIERS QPQ,
17768: qp_npreq_line_attrs_tmp QPLAT_Q,
17769: qp_npreq_line_attrs_tmp QPLAT_PROD,
17770: QP_PRICING_ATTRIBUTES QPA,
17889: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
17890: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
17891: ,QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
17892: FROM
17893: qp_npreq_lines_tmp QPLINES,
17894: QP_QUALIFIERS QPQ,
17895: qp_npreq_line_attrs_tmp QPLAT_Q,
17896: qp_npreq_line_attrs_tmp QPLAT_PROD,
17897: QP_PRICING_ATTRIBUTES QPA,
18025: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
18026: ,qplat_prod.DERIVED_QUALIFIER_FLAG -- Added for TCA
18027: ,QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
18028: FROM
18029: qp_npreq_lines_tmp QPLINES,
18030: qp_npreq_line_attrs_tmp qplat_prod,
18031: QP_PRICING_ATTRIBUTES QPA,
18032: QP_LIST_LINES QPL,
18033: QP_LIST_HEADERS_B QPH,
18127: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
18128: ,qplat_pric.DERIVED_QUALIFIER_FLAG -- Added for TCA
18129: ,QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
18130: FROM
18131: qp_npreq_lines_tmp qplines,
18132: qp_npreq_line_attrs_tmp qplat_prod,
18133: QP_PRICING_ATTRIBUTES qpa,
18134: qp_npreq_line_attrs_tmp qplat_pric,
18135: QP_LIST_LINES qpl,
18248: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
18249: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
18250: ,QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
18251: FROM
18252: qp_npreq_lines_tmp QPLINES,
18253: QP_QUALIFIERS QPQ,
18254: qp_npreq_line_attrs_tmp QPLAT_Q,
18255: qp_npreq_line_attrs_tmp QPLAT_PROD,
18256: QP_PRICING_ATTRIBUTES QPA,
18433: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
18434: ,QPLAT_Q.DERIVED_QUALIFIER_FLAG -- Added for TCA
18435: ,QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
18436: FROM
18437: qp_npreq_lines_tmp QPLINES,
18438: QP_QUALIFIERS QPQ,
18439: qp_npreq_line_attrs_tmp QPLAT_Q,
18440: qp_npreq_line_attrs_tmp QPLAT_PROD,
18441: QP_PRICING_ATTRIBUTES QPA,
18627: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
18628: ,qplat_prod.DERIVED_QUALIFIER_FLAG -- Added for TCA
18629: ,QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
18630: FROM
18631: qp_npreq_lines_tmp QPLINES,
18632: qp_npreq_line_attrs_tmp qplat_prod,
18633: QP_PRICING_ATTRIBUTES QPA,
18634: QP_LIST_LINES QPL,
18635: QP_LIST_HEADERS_B QPH,
18790: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
18791: ,qplat_pric.DERIVED_QUALIFIER_FLAG -- Added for TCA
18792: ,QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
18793: FROM
18794: qp_npreq_lines_tmp qplines,
18795: qp_npreq_line_attrs_tmp qplat_prod,
18796: QP_PRICING_ATTRIBUTES qpa,
18797: qp_npreq_line_attrs_tmp qplat_pric,
18798: QP_LIST_LINES qpl,
18909: INDX,QP_PREQ_GRP.list_header_search.l_validated_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
18910: INDX,QP_PREQ_GRP.list_header_search.l_validated_cur,qp_npreq_line_attrs_tmp_N2,CONTEXT,3
18911: INDX,QP_PREQ_GRP.list_header_search.l_validated_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE,4
18912:
18913: INDX,QP_PREQ_GRP.list_header_search.l_validated_cur,qp_npreq_lines_tmp_U1,LINE_INDEX,1
18914: INDX,QP_PREQ_GRP.list_header_search.l_validated_cur,qp_npreq_lines_tmp_U1,LINE_TYPE_CODE,2
18915: */
18916:
18917: CURSOR l_validated_cur IS
18910: INDX,QP_PREQ_GRP.list_header_search.l_validated_cur,qp_npreq_line_attrs_tmp_N2,CONTEXT,3
18911: INDX,QP_PREQ_GRP.list_header_search.l_validated_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE,4
18912:
18913: INDX,QP_PREQ_GRP.list_header_search.l_validated_cur,qp_npreq_lines_tmp_U1,LINE_INDEX,1
18914: INDX,QP_PREQ_GRP.list_header_search.l_validated_cur,qp_npreq_lines_tmp_U1,LINE_TYPE_CODE,2
18915: */
18916:
18917: CURSOR l_validated_cur IS
18918: SELECT /*+ dynamic_sampling(1) */ LINE_INDEX,
18932: AND ATTRIBUTE IN (G_PROMOTION_ATTRIBUTE,G_QUAL_ATTRIBUTE2)
18933: AND ATTRIBUTE_TYPE = G_QUALIFIER_TYPE
18934: AND EXISTS
18935: (Select /*+ dynamic_sampling(1) */ 'X'
18936: FROM qp_npreq_lines_tmp b
18937: WHERE b.line_index = a.line_index
18938: AND (b.price_flag = G_YES
18939: OR
18940: (b.price_flag = G_PHASE
18952: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,qp_npreq_line_attrs_tmp_N1,LINE_INDEX,1
18953: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,qp_npreq_line_attrs_tmp_N1,ATTRIBUTE_TYPE,2
18954: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,qp_npreq_line_attrs_tmp_N1,PRICING_STATUS_CODE,3
18955:
18956: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
18957: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,qp_npreq_lines_tmp_N1,LINE_TYPE_CODE,2
18958:
18959: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
18960: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
18953: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,qp_npreq_line_attrs_tmp_N1,ATTRIBUTE_TYPE,2
18954: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,qp_npreq_line_attrs_tmp_N1,PRICING_STATUS_CODE,3
18955:
18956: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
18957: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,qp_npreq_lines_tmp_N1,LINE_TYPE_CODE,2
18958:
18959: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
18960: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
18961: INDX,QP_PREQ_GRP.list_header_search.l_other_list_cur,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
19028: NULL PRICING_ATTRIBUTE_TYPE,
19029: NULL PRICING_OPERATOR_TYPE,
19030: NULL PRICING_ATTRIBUTE_DATATYPE
19031: FROM
19032: qp_npreq_lines_tmp QPLINES,
19033: qp_npreq_line_attrs_tmp QPLAT_PROD,
19034: QP_PRICING_ATTRIBUTES QPA,
19035: QP_LIST_HEADERS_B QPH,
19036: QP_LIST_LINES QPL,
19145: QPLAT_PRIC.ATTRIBUTE_TYPE PRICING_ATTRIBUTE_TYPE,
19146: QPA.COMPARISON_OPERATOR_CODE PRICING_OPERATOR_TYPE,
19147: QPA.PRICING_ATTRIBUTE_DATATYPE PRICING_ATTRIBUTE_DATATYPE
19148: FROM
19149: qp_npreq_lines_tmp QPLINES,
19150: qp_npreq_line_attrs_tmp QPLAT_PROD,
19151: QP_PRICING_ATTRIBUTES QPA,
19152: qp_npreq_line_attrs_tmp QPLAT_PRIC,
19153: QP_LIST_HEADERS_B QPH,
19276: NULL PRICING_ATTRIBUTE_TYPE,
19277: NULL PRICING_OPERATOR_TYPE,
19278: NULL PRICING_ATTRIBUTE_DATATYPE
19279: FROM
19280: qp_npreq_lines_tmp QPLINES,
19281: qp_npreq_line_attrs_tmp QPLAT_PROD,
19282: QP_PRICING_ATTRIBUTES QPA,
19283: QP_LIST_HEADERS_B QPH,
19284: QP_LIST_LINES QPL,
19393: QPLAT_PRIC.ATTRIBUTE_TYPE PRICING_ATTRIBUTE_TYPE,
19394: QPA.COMPARISON_OPERATOR_CODE PRICING_OPERATOR_TYPE,
19395: QPA.PRICING_ATTRIBUTE_DATATYPE PRICING_ATTRIBUTE_DATATYPE
19396: FROM
19397: qp_npreq_lines_tmp QPLINES,
19398: qp_npreq_line_attrs_tmp QPLAT_PROD,
19399: QP_PRICING_ATTRIBUTES QPA,
19400: qp_npreq_line_attrs_tmp QPLAT_PRIC,
19401: QP_LIST_HEADERS_B QPH,
19472: INDX,QP_PREQ_GRP.list_header_search.l_check_no_list_passed,- No Index Used-,NA,NA
19473: */
19474: CURSOR L_CHECK_NO_LIST_PASSED IS
19475: SELECT 'X'
19476: FROM qp_npreq_lines_tmp A
19477: WHERE PROCESSED_CODE IN (G_NO_LIST_PASSED,G_STS_LHS_NOT_FOUND)
19478: AND LINE_TYPE_CODE <> G_ORDER_LEVEL
19479: AND (instr(PROCESS_STATUS,'FREEGOOD')=0); -- bug 2693700
19480:
19482: INDX,QP_PREQ_GRP.list_header_search.l_check_secondary_search,- No Index Used-,NA,NA
19483: */
19484: CURSOR l_check_secondary_search IS
19485: SELECT 'X'
19486: FROM qp_npreq_lines_tmp
19487: WHERE PROCESSED_CODE = G_STS_LHS_NOT_FOUND
19488: AND LINE_TYPE_CODE <> G_ORDER_LEVEL
19489: AND (instr(PROCESS_STATUS,'FREEGOOD')=0); -- bug 2693700
19490:
20914: -- 3773652
20915: -- re-run Evaluate_Between after UOM conversion to check PLL really qualifies
20916: begin
20917: select 'x' into l_dummy
20918: from qp_npreq_lines_tmp
20919: where LINE_UOM_CODE <> nvl(PRICED_UOM_CODE, LINE_UOM_CODE)
20920: and (PRICE_FLAG = G_YES
20921: or
20922: PRICE_FLAG = G_PHASE and p_freeze_override_flag = G_YES)
21095: qplines.pricing_effective_date,
21096: qpq.qualifier_precedence,
21097: atrgrps.descendents_quals_exist
21098: FROM
21099: qp_npreq_lines_tmp qplines,
21100: qp_npreq_line_patrns_tmp qppattmp,
21101: QP_QUALIFIERS qpq,
21102: qp_secu_attr_groups_v atrgrps,
21103: --qp_attribute_groups atrgrps,
21182: qplines.pricing_effective_date,
21183: qppqual.qualifier_precedence,
21184: 'N' descendents_quals_exist,
21185: qpl.PRICE_BREAK_TYPE_CODE
21186: FROM qp_npreq_lines_tmp qplines,
21187: qp_npreq_line_patrns_tmp qppattmp,
21188: qp_preq_patrn_qual_tmp qppqual,
21189: qp_list_lines qpl
21190: WHERE qppattmp.LINE_INDEX = qplines.LINE_INDEX
21242: qplines.pricing_effective_date,
21243: qpq.qualifier_precedence,
21244: atrgrps.descendents_quals_exist
21245: FROM
21246: qp_npreq_lines_tmp qplines,
21247: qp_npreq_line_patrns_tmp qppattmp,
21248: QP_QUALIFIERS qpq,
21249: qp_secu_attr_groups_v atrgrps,
21250: --qp_attribute_groups atrgrps,
21385: qppqual.BASE_CURRENCY_CODE,
21386: qppqual.qualifier_precedence,
21387: 'N' descendents_quals_exist,
21388: qpl.PRICE_BREAK_TYPE_CODE
21389: FROM qp_npreq_lines_tmp qplines,
21390: qp_npreq_line_patrns_tmp qppattmp,
21391: qp_preq_patrn_qual_tmp qppqual,
21392: qp_list_lines qpl
21393: WHERE qppattmp.LINE_INDEX = qplines.LINE_INDEX
21444: qplines.pricing_effective_date,
21445: qpq.qualifier_precedence,
21446: 'N' descendents_quals_exist
21447: FROM
21448: qp_npreq_lines_tmp qplines,
21449: QP_QUALIFIERS qpq,
21450: qp_list_headers_b qph,
21451: qp_pte_source_systems qprs,
21452: --QP_PRICE_REQ_SOURCES_V qprs, /* yangli */
21570: qppqual.BASE_CURRENCY_CODE,
21571: qppqual.qualifier_precedence,
21572: 'N' descendents_quals_exist,
21573: qpl.PRICE_BREAK_TYPE_CODE
21574: FROM qp_npreq_lines_tmp qplines,
21575: qp_npreq_line_patrns_tmp qppattmp,
21576: qp_preq_patrn_qual_tmp qppqual,
21577: qp_list_lines qpl
21578: WHERE qppattmp.LINE_INDEX = qplines.LINE_INDEX
21628: qplines.pricing_effective_date,
21629: qpq.qualifier_precedence,
21630: 'N' descendents_quals_exist
21631: FROM
21632: qp_npreq_lines_tmp qplines,
21633: QP_QUALIFIERS qpq,
21634: qp_list_headers_b qph,
21635: qp_pte_source_systems qprs
21636: --QP_PRICE_REQ_SOURCES_V qprs /* yangli */
21697: qplines.pricing_effective_date,
21698: qppqual.qualifier_precedence,
21699: 'N' descendents_quals_exist,
21700: qpl.PRICE_BREAK_TYPE_CODE
21701: FROM qp_npreq_lines_tmp qplines,
21702: qp_npreq_line_patrns_tmp qppattmp,
21703: qp_preq_patrn_qual_tmp qppqual,
21704: qp_list_lines qpl
21705: WHERE qppattmp.LINE_INDEX = qplines.LINE_INDEX
21772: QPLINES.CURRENCY_CODE ORDER_CURRENCY,
21773: qpq.qualifier_precedence,
21774: 'N' descendents_quals_exist,
21775: qpl.PRICE_BREAK_TYPE_CODE
21776: FROM qp_npreq_lines_tmp qplines,
21777: QP_QUALIFIERS QPQ,
21778: qp_npreq_line_patrns_tmp qppattmp1,
21779: qp_list_lines qpl,
21780: qp_secu_attr_groups_v qpatrgrp,
21877: QPLINES.CURRENCY_CODE ORDER_CURRENCY,
21878: qpq.qualifier_precedence,
21879: 'N' descendents_quals_exist,
21880: qpl.PRICE_BREAK_TYPE_CODE
21881: FROM qp_npreq_lines_tmp qplines,
21882: QP_QUALIFIERS QPQ,
21883: qp_npreq_line_patrns_tmp qppattmp1,
21884: qp_list_lines qpl,
21885: qp_secu_attr_groups_v qpatrgrp,
23381: -- 3773652
23382: -- re-run Evaluate_Between after UOM conversion to check PLL really qualifies
23383: begin
23384: select 'x' into l_dummy
23385: from qp_npreq_lines_tmp
23386: where LINE_UOM_CODE <> nvl(PRICED_UOM_CODE, LINE_UOM_CODE)
23387: and (PRICE_FLAG = G_YES
23388: or
23389: PRICE_FLAG = G_PHASE and p_freeze_override_flag = G_YES)
23590: qpl.BREAK_UOM_ATTRIBUTE,
23591: QPLINES.CURRENCY_CODE ORDER_CURRENCY,
23592: qpatrgrp.descendents_quals_exist,
23593: QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
23594: FROM qp_npreq_lines_tmp qplines,
23595: qp_npreq_line_patrns_tmp qppattmp1,
23596: qp_list_lines qpl,
23597: qp_secu_attr_groups_v qpatrgrp,
23598: --qp_attribute_groups qpatrgrp,
23672: qpl.BREAK_UOM_ATTRIBUTE,
23673: QPLINES.CURRENCY_CODE ORDER_CURRENCY,
23674: 'N' descendents_quals_exist,
23675: QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
23676: FROM qp_npreq_lines_tmp qplines,
23677: qp_npreq_line_patrns_tmp qppattmp1,
23678: qp_list_lines qpl,
23679: qp_list_headers_b qph,
23680: qp_pte_source_systems qprs
23751: qpl.BREAK_UOM_ATTRIBUTE,
23752: QPLINES.CURRENCY_CODE ORDER_CURRENCY,
23753: qpatrgrp.descendents_quals_exist,
23754: QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
23755: FROM qp_npreq_lines_tmp qplines,
23756: qp_npreq_line_patrns_tmp qppattmp1,
23757: qp_list_lines qpl,
23758: qp_secu_attr_groups_v qpatrgrp,
23759: --qp_attribute_groups qpatrgrp,
23888: qpl.BREAK_UOM_ATTRIBUTE,
23889: QPLINES.CURRENCY_CODE ORDER_CURRENCY,
23890: 'N' descendents_quals_exist,
23891: QPL.PRICE_BREAK_TYPE_CODE --[julin] for price book
23892: FROM qp_npreq_lines_tmp qplines,
23893: qp_npreq_line_patrns_tmp qppattmp1,
23894: qp_list_lines qpl,
23895: qp_list_headers_b qph,
23896: --qp_price_req_sources_v qprs,
24446: -- 3773652
24447: -- re-run Evaluate_Between after UOM conversion to check PLL really qualifies
24448: begin
24449: select 'x' into l_dummy
24450: from qp_npreq_lines_tmp
24451: where LINE_UOM_CODE <> nvl(PRICED_UOM_CODE, LINE_UOM_CODE)
24452: and (PRICE_FLAG = G_YES
24453: or
24454: PRICE_FLAG = G_PHASE and p_freeze_override_flag = G_YES)
25482: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,qp_npreq_line_attrs_tmp_N2,VALUE_FROM,6
25483:
25484: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,QP_LIST_LINES_PK,LIST_LINE_ID,1
25485:
25486: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
25487: */
25488:
25489: 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
25490: -- Product and Qualifier Only. No pricing
25545: and list_line_id > 0 ) qpq,
25546: qp_pricing_attributes qpprod,
25547: qp_npreq_line_attrs_tmp qplatpr,
25548: qp_list_lines qpl,
25549: qp_npreq_lines_tmp qplines
25550: where
25551: qpprod.list_header_id = qpq.list_header_id
25552: and qpprod.list_line_id = qpq.list_line_id
25553: and qpl.list_line_id = qpprod.list_line_id
25636: and list_line_id < 0 ) qpq,
25637: qp_npreq_line_attrs_tmp qplatpr,
25638: qp_pricing_attributes qpprod,
25639: qp_list_lines qpl,
25640: qp_npreq_lines_tmp qplines
25641: where
25642: qpprod.list_header_id = qpq.list_header_id
25643: and qpl.list_line_id = qpprod.list_line_id
25644: and (qpl.modifier_level_code = qplines.line_type_code
25694: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,qp_npreq_line_attrs_tmp_N2,VALUE_FROM,6
25695:
25696: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_LIST_LINES_PK,LIST_LINE_ID,1
25697:
25698: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,qp_npreq_lines_tmp_U1,LINE_INDEX,1
25699: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,qp_npreq_lines_tmp_U1,LINE_TYPE_CODE,2
25700:
25701: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,QP_PREQ_QUAL_TMP_N5,PRICING_STATUS_CODE,1
25702: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,QP_PREQ_QUAL_TMP_N5,SEARCH_IND,2
25695:
25696: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,QP_LIST_LINES_PK,LIST_LINE_ID,1
25697:
25698: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,qp_npreq_lines_tmp_U1,LINE_INDEX,1
25699: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,qp_npreq_lines_tmp_U1,LINE_TYPE_CODE,2
25700:
25701: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,QP_PREQ_QUAL_TMP_N5,PRICING_STATUS_CODE,1
25702: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,QP_PREQ_QUAL_TMP_N5,SEARCH_IND,2
25703: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,QP_PREQ_QUAL_TMP_N5,LINE_INDEX,5
25716: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel1,qp_npreq_line_attrs_tmp_N2,VALUE_FROM,6
25717:
25718: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,QP_LIST_LINES_PK,LIST_LINE_ID,1
25719:
25720: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,qp_npreq_lines_tmp_U1,LINE_INDEX,1
25721: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,qp_npreq_lines_tmp_U1,LINE_TYPE_CODE,2
25722: */
25723: CURSOR l_prod_qual_pric_cur(p_pricing_phase_id NUMBER, p_list_header_id NUMBER, p_list_line_id NUMBER,
25724: p_line_index NUMBER) IS
25717:
25718: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,QP_LIST_LINES_PK,LIST_LINE_ID,1
25719:
25720: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,qp_npreq_lines_tmp_U1,LINE_INDEX,1
25721: INDX,QP_PREQ_GRP.select_modifiers.l_prod_qual_pric_cur_sel2,qp_npreq_lines_tmp_U1,LINE_TYPE_CODE,2
25722: */
25723: CURSOR l_prod_qual_pric_cur(p_pricing_phase_id NUMBER, p_list_header_id NUMBER, p_list_line_id NUMBER,
25724: p_line_index NUMBER) IS
25725: --Product+Qualifiers+Pricing
25792: QP_PRICING_ATTRIBUTES qpprod ,
25793: qp_npreq_line_attrs_tmp qplatpr,
25794: qp_npreq_line_attrs_tmp qplatpc,
25795: qp_list_lines qpl ,
25796: qp_npreq_lines_tmp qplines
25797: where
25798: qpprod.list_header_id = qpq.list_header_id
25799: and qpprod.list_line_id = qpq.list_line_id
25800: and qpl.list_line_id = qpprod.list_line_id
25907: qp_npreq_line_attrs_tmp qplatpr,
25908: QP_PRICING_ATTRIBUTES qpprod ,
25909: qp_npreq_line_attrs_tmp qplatpc,
25910: qp_list_lines qpl ,
25911: qp_npreq_lines_tmp qplines
25912: where
25913: qpprod.list_header_id = qpq.list_header_id
25914: and qpl.list_line_id = qpprod.list_line_id
25915: and (qpl.modifier_level_code = qplines.line_type_code
26122: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
26123:
26124: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
26125:
26126: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
26127: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,qp_npreq_lines_tmp_N1,LINE_TYPE_CODE,2
26128:
26129: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
26130: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICE_REQ_SOURCES_PK,SOURCE_SYSTEM_CODE,2
26123:
26124: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
26125:
26126: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
26127: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,qp_npreq_lines_tmp_N1,LINE_TYPE_CODE,2
26128:
26129: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
26130: INDX,QP_PREQ_GRP.select_modifiers.l_prod_cur,QP_PRICE_REQ_SOURCES_PK,SOURCE_SYSTEM_CODE,2
26131: */
26182: qpl.accum_context, -- accum range break
26183: qpl.accum_attribute, -- accum range break
26184: qpl.accum_attr_run_src_flag -- accum range break
26185: FROM
26186: qp_npreq_lines_tmp QPLINES,
26187: qp_npreq_line_attrs_tmp qplatpr,
26188: QP_PRICING_ATTRIBUTES QPPROD ,
26189: QP_LIST_HEADERS_B QPH ,
26190: QP_LIST_LINES QPL ,
26286: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,qp_npreq_line_attrs_tmp_N2,LINE_INDEX,5
26287:
26288: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
26289:
26290: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
26291: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,qp_npreq_lines_tmp_N1,LINE_TYPE_CODE,2
26292:
26293: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
26294: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICE_REQ_SOURCES_PK,SOURCE_SYSTEM_CODE,2
26287:
26288: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
26289:
26290: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
26291: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,qp_npreq_lines_tmp_N1,LINE_TYPE_CODE,2
26292:
26293: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
26294: INDX,QP_PREQ_GRP.select_modifiers.l_prod_pric_cur,QP_PRICE_REQ_SOURCES_PK,SOURCE_SYSTEM_CODE,2
26295: */
26356: qpl.accum_context, -- accum range break
26357: qpl.accum_attribute, -- accum range break
26358: qpl.accum_attr_run_src_flag -- accum range break
26359: FROM
26360: qp_npreq_lines_tmp QPLINES,
26361: qp_npreq_line_attrs_tmp qplatpr,
26362: QP_PRICING_ATTRIBUTES QPPROD ,
26363: qp_npreq_line_attrs_tmp qplatpc,
26364: QP_LIST_HEADERS_B QPH ,
26496: qpl.accum_context, -- accum range break
26497: qpl.accum_attribute, -- accum range break
26498: qpl.accum_attr_run_src_flag -- accum range break
26499: from
26500: qp_npreq_lines_tmp qplines,
26501: qp_list_lines qpl,
26502: qp_list_headers_b qph,
26503: --qp_price_req_sources qprs
26504: qp_price_req_sources_v qprs /* yangli */
26653: INDX,QP_PREQ_GRP.select_modifiers.l_get_line_index_for_quals,qp_npreq_line_attrs_tmp_N2,VALUE_FROM,6
26654:
26655: INDX,QP_PREQ_GRP.select_modifiers.l_get_line_index_for_quals,QP_PREQ_QUAL_TMP_N3,PRICING_STATUS_CODE,1
26656:
26657: -- Full table scan on qp_npreq_lines_tmp
26658: */
26659:
26660: --hw
26661:
26701: NULL ACTIVE_DATE_FIRST_TYPE,
26702: NULL ACTIVE_DATE_SECOND_TYPE,
26703: c.HEADER_LIMIT_EXISTS,
26704: a.DERIVED_QUALIFIER_FLAG -- Added for TCA
26705: FROM qp_npreq_lines_tmp b,
26706: QP_PREQ_QUAL_TMP c ,
26707: qp_npreq_line_attrs_tmp a
26708: WHERE a.CONTEXT = c.QUALIFIER_ATTRIBUTE_CONTEXT
26709: AND a.ATTRIBUTE = c.QUALIFIER_ATTRIBUTE
26792: NULL END_DATE_ACTIVE_SECOND_S,
26793: NULL ACTIVE_DATE_FIRST_TYPE,
26794: NULL ACTIVE_DATE_SECOND_TYPE,
26795: c.HEADER_LIMIT_EXISTS
26796: FROM qp_npreq_lines_tmp b,
26797: QP_PREQ_QUAL_TMP c ,
26798: qp_npreq_line_attrs_tmp a
26799: WHERE a.CONTEXT = c.QUALIFIER_ATTRIBUTE_CONTEXT
26800: AND a.ATTRIBUTE = c.QUALIFIER_ATTRIBUTE
26882: NULL END_DATE_ACTIVE_SECOND_S,
26883: NULL ACTIVE_DATE_FIRST_TYPE,
26884: NULL ACTIVE_DATE_SECOND_TYPE,
26885: c.HEADER_LIMIT_EXISTS
26886: FROM qp_npreq_lines_tmp b,
26887: QP_PREQ_QUAL_TMP c ,
26888: qp_npreq_line_attrs_tmp a
26889: WHERE a.CONTEXT = c.QUALIFIER_ATTRIBUTE_CONTEXT
26890: AND a.ATTRIBUTE = c.QUALIFIER_ATTRIBUTE
29471: qplines.pricing_effective_date,
29472: atrgrps.descendents_quals_exist,
29473: atrgrps.effective_precedence --bug 12731268
29474: FROM (SELECT /*+ dynamic_sampling(1) */ *
29475: FROM qp_npreq_lines_tmp
29476: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED) --check this condition
29477: AND nvl(validated_flag,G_NO) = G_NO) qplines,
29478: qp_npreq_line_patrns_tmp qppattmp,
29479: qp_secu_attr_groups_v atrgrps,
29541: qppqual.BASE_CURRENCY_CODE,
29542: qplines.pricing_effective_date,
29543: QPL.PRICE_BREAK_TYPE_CODE,
29544: 'N' descendents_quals_exist
29545: FROM qp_npreq_lines_tmp qplines,
29546: qp_npreq_line_patrns_tmp qppat,
29547: qp_preq_patrn_qual_tmp qppqual,
29548: qp_list_lines qpl
29549: WHERE qplines.processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
29614: QPLINES.CURRENCY_CODE ORDER_CURRENCY,
29615: qpl.PRICE_BREAK_TYPE_CODE,
29616: qpatrgrp.descendents_quals_exist,
29617: qpatrgrp.effective_precedence --bug 12731268
29618: FROM qp_npreq_lines_tmp qplines,
29619: qp_npreq_line_patrns_tmp qppattmp1,
29620: qp_list_lines qpl,
29621: qp_secu_attr_groups_v qpatrgrp,
29622: --qp_attribute_groups qpatrgrp,
29712: QPLINES.CURRENCY_CODE ORDER_CURRENCY,
29713: qpl.PRICE_BREAK_TYPE_CODE,
29714: qpatrgrp.descendents_quals_exist,
29715: qpatrgrp.effective_precedence --bug 12731268
29716: FROM qp_npreq_lines_tmp qplines,
29717: qp_npreq_line_patrns_tmp qppattmp1,
29718: qp_list_lines qpl,
29719: qp_secu_attr_groups_v qpatrgrp,
29720: --qp_attribute_groups qpatrgrp,
29840: qplines.pricing_effective_date,
29841: atrgrps.descendents_quals_exist,
29842: atrgrps.effective_precedence --bug 12731268
29843: FROM (SELECT /*+ dynamic_sampling(1) */ *
29844: FROM qp_npreq_lines_tmp
29845: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED) --check this condition
29846: AND nvl(validated_flag,G_NO) = G_NO) qplines,
29847: qp_npreq_line_patrns_tmp qppattmp,
29848: qp_secu_attr_groups_v atrgrps,
29962: qppqual.BASE_CURRENCY_CODE,
29963: qplines.pricing_effective_date,
29964: qpl.PRICE_BREAK_TYPE_CODE,
29965: 'N' descendents_quals_exist
29966: FROM qp_npreq_lines_tmp qplines,
29967: qp_npreq_line_patrns_tmp qppat,
29968: qp_preq_patrn_qual_tmp qppqual,
29969: qp_list_lines qpl
29970: WHERE qplines.processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
30939: -- 3773652
30940: -- re-run Evaluate_Between after UOM conversion to check PLL really qualifies
30941: begin
30942: select 'x' into l_dummy
30943: from qp_npreq_lines_tmp
30944: where LINE_UOM_CODE <> nvl(PRICED_UOM_CODE, LINE_UOM_CODE)
30945: and (PRICE_FLAG = G_YES
30946: or
30947: PRICE_FLAG = G_PHASE and p_freeze_override_flag = G_YES)
31146: ,QPL.BREAK_UOM_CONTEXT /* Proration */
31147: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
31148: FROM --[julin/5007789]
31149: (SELECT /*+ dynamic_sampling(1) */ *
31150: FROM qp_npreq_lines_tmp
31151: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
31152: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
31153: qp_npreq_line_attrs_tmp qplatq,
31154: qp_qualifiers qpq,
31256: ,QPL.BREAK_UOM_CONTEXT /* Proration */
31257: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
31258: FROM --[julin/5007789]
31259: (SELECT /*+ dynamic_sampling(1) */ *
31260: FROM qp_npreq_lines_tmp
31261: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
31262: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
31263: qp_npreq_line_attrs_tmp qplatq,
31264: qp_qualifiers qpq,
31366: ,QPL.BREAK_UOM_CONTEXT /* Proration */
31367: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
31368: FROM --[julin/5007789]
31369: (SELECT /*+ dynamic_sampling(1) */ *
31370: FROM qp_npreq_lines_tmp
31371: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
31372: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
31373: qp_npreq_line_attrs_tmp qplatq,
31374: qp_qualifiers qpq,
31495: ,QPL.BREAK_UOM_CONTEXT /* Proration */
31496: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
31497: FROM --[julin/5007789]
31498: (SELECT /*+ dynamic_sampling(1) */ *
31499: FROM qp_npreq_lines_tmp
31500: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
31501: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
31502: qp_npreq_line_attrs_tmp qplatq,
31503: qp_qualifiers qpq,
31652: ,QPL.BREAK_UOM_CONTEXT /* Proration */
31653: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
31654: FROM --[julin/5007789]
31655: (SELECT /*+ dynamic_sampling(1) */ *
31656: FROM qp_npreq_lines_tmp
31657: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
31658: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
31659: qp_npreq_line_attrs_tmp qplatq,
31660: qp_qualifiers qpq,
31809: ,QPL.BREAK_UOM_CONTEXT /* Proration */
31810: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
31811: FROM --[julin/5007789]
31812: (SELECT /*+ dynamic_sampling(1) */ *
31813: FROM qp_npreq_lines_tmp
31814: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
31815: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
31816: qp_npreq_line_attrs_tmp qplatq,
31817: qp_qualifiers qpq,
31979: ,QPL.BREAK_UOM_CONTEXT /* Proration */
31980: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
31981: FROM --[julin/5007789]
31982: (SELECT /*+ dynamic_sampling(1) */ *
31983: FROM qp_npreq_lines_tmp
31984: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
31985: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
31986: qp_npreq_line_attrs_tmp qplatq,
31987: qp_qualifiers qpq ,
32107: ,QPL.BREAK_UOM_CONTEXT /* Proration */
32108: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
32109: FROM --[julin/5007789]
32110: (SELECT /*+ dynamic_sampling(1) */ *
32111: FROM qp_npreq_lines_tmp
32112: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
32113: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
32114: qp_npreq_line_attrs_tmp qplatq,
32115: qp_qualifiers qpq ,
32235: ,QPL.BREAK_UOM_CONTEXT /* Proration */
32236: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
32237: FROM --[julin/5007789]
32238: (SELECT /*+ dynamic_sampling(1) */ *
32239: FROM qp_npreq_lines_tmp
32240: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
32241: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
32242: qp_npreq_line_attrs_tmp qplatq,
32243: qp_qualifiers qpq ,
32383: ,QPL.BREAK_UOM_CONTEXT /* Proration */
32384: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
32385: FROM --[julin/5007789]
32386: (SELECT /*+ dynamic_sampling(1) */ *
32387: FROM qp_npreq_lines_tmp
32388: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
32389: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
32390: qp_npreq_line_attrs_tmp qplatq,
32391: qp_qualifiers qpq ,
32558: ,QPL.BREAK_UOM_CONTEXT /* Proration */
32559: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
32560: FROM --[julin/5007789]
32561: (SELECT /*+ dynamic_sampling(1) */ *
32562: FROM qp_npreq_lines_tmp
32563: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
32564: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
32565: qp_npreq_line_attrs_tmp qplatq,
32566: qp_qualifiers qpq ,
32733: ,QPL.BREAK_UOM_CONTEXT /* Proration */
32734: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
32735: FROM --[julin/5007789]
32736: (SELECT /*+ dynamic_sampling(1) */ *
32737: FROM qp_npreq_lines_tmp
32738: WHERE processed_code IN (G_STS_LHS_NOT_FOUND, G_NO_LIST_PASSED)
32739: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
32740: qp_npreq_line_attrs_tmp qplatq,
32741: qp_qualifiers qpq ,
32890: ,QPL.BREAK_UOM_CONTEXT /* Proration */
32891: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
32892: FROM --[julin/5000789]
32893: (SELECT /*+ dynamic_sampling(1) */ *
32894: FROM qp_npreq_lines_tmp
32895: WHERE processed_code IN (G_STS_LHS_NOT_FOUND,G_NO_LIST_PASSED)
32896: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
32897: qp_npreq_line_attrs_tmp qplatpr,
32898: qp_pricing_attributes qpprod ,
32977: ,QPL.BREAK_UOM_CONTEXT /* Proration */
32978: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
32979: FROM --[julin/5007789]
32980: (SELECT /*+ dynamic_sampling(1) */ *
32981: FROM qp_npreq_lines_tmp
32982: WHERE processed_code IN (G_STS_LHS_NOT_FOUND,G_NO_LIST_PASSED)
32983: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
32984: qp_npreq_line_attrs_tmp qplatpr,
32985: qp_pricing_attributes qpprod,
33111: ,QPL.BREAK_UOM_CONTEXT /* Proration */
33112: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
33113: FROM --[julin/5007789]
33114: (SELECT /*+ dynamic_sampling(1) */ *
33115: FROM qp_npreq_lines_tmp
33116: WHERE processed_code IN (G_STS_LHS_NOT_FOUND,G_NO_LIST_PASSED)
33117: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
33118: qp_npreq_line_attrs_tmp qplatpr,
33119: qp_pricing_attributes qpprod,
33217: ,QPL.BREAK_UOM_CONTEXT /* Proration */
33218: ,QPL.BREAK_UOM_ATTRIBUTE /* Proration */
33219: FROM --[julin/5007789]
33220: (SELECT /*+ dynamic_sampling(1) */ *
33221: FROM qp_npreq_lines_tmp
33222: WHERE processed_code IN (G_STS_LHS_NOT_FOUND,G_NO_LIST_PASSED)
33223: AND (nvl(validated_flag,G_NO) = G_NO or nvl(QP_PREQ_PUB.G_CALL_FROM_PRICE_BOOK, G_NO) = G_YES)) qplines,
33224: qp_npreq_line_attrs_tmp qplatpr,
33225: qp_pricing_attributes qpprod ,
35026: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_line_index_cur,- No Index Used -,NA,NA
35027: */
35028: CURSOR l_get_line_index_cur IS
35029: SELECT line_index
35030: FROM qp_npreq_lines_tmp
35031: WHERE line_type_code <> G_ORDER_LEVEL;
35032:
35033: /*
35034: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_check_modlist_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
35059:
35060: -- find line_index(s) whose passed-in price list has not been qualified
35061: CURSOR l_check_no_modlist_cur IS
35062: SELECT /*+ index(qpll QP_PREQ_LINES_TMP_N2) */ qpll.line_index
35063: FROM qp_npreq_lines_tmp qpll
35064: WHERE qpll.line_type_code <> G_ORDER_LEVEL
35065: AND NOT EXISTS (
35066: SELECT /*+ ORDERED index(qpld QP_PREQ_LDETS_TMP_N1) index(qplat qp_preq_line_attrs_tmp_n2) l_check_modlist_cur */ 'X'
35067: FROM qp_npreq_ldets_tmp qpld,
35144:
35145: BEGIN
35146: qp_debug_util.tstart('CREATE_QUALIFIER_FROM_LIST','Procedure CREATE_QUALIFIER_FROM_LIST');
35147: --po integration start
35148: UPDATE qp_npreq_lines_tmp a
35149: SET pricing_status_code = G_STATUS_UPDATED
35150: Where list_price_override_flag in ('Y', 'O') --po integration
35151: AND line_type_code <> G_ORDER_LEVEL;
35152: --po integration end
35240:
35241: -- julin [3805113]: bulk update
35242: IF (l_a_list_header_id_tbl.COUNT > 0) THEN
35243: FORALL I IN l_a_list_header_id_tbl.first..l_a_list_header_id_tbl.last
35244: UPDATE qp_npreq_lines_tmp
35245: SET price_list_header_id = l_a_list_header_id_tbl(I)
35246: WHERE line_index = l_a_line_index_tbl(I);
35247: END IF;
35248:
35397: qplt.ROUNDING_FACTOR,
35398: qplt.CATCHWEIGHT_QTY,
35399: qplt.ACTUAL_ORDER_QUANTITY,
35400: NVL(qplt.price_flag,G_YES)
35401: FROM qp_npreq_lines_tmp qplt
35402: WHERE qplt.PRICING_STATUS_CODE IN (G_STATUS_UNCHANGED, G_STATUS_UPDATED)
35403: AND (qplt.PRICE_FLAG IN (G_YES,G_PHASE)
35404: OR qplt.PROCESSED_CODE = G_BY_ENGINE)
35405: AND nvl(qplt.USAGE_PRICING_TYPE,G_REGULAR_USAGE_TYPE) NOT IN
35410: /*
35411: INDX,QP_PREQ_GRP.call_calculation_engine.l_request_line_detail_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
35412: INDX,QP_PREQ_GRP.call_calculation_engine.l_request_line_detail_cur,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
35413:
35414: INDX,QP_PREQ_GRP.call_calculation_engine.l_request_line_detail_cur,qp_npreq_lines_tmp_N2,LINE_TYPE_CODE,1
35415: */
35416: CURSOR l_request_line_detail_cur(L_LINE_INDEX PLS_INTEGER,
35417: p_price_flag VARCHAR2) IS
35418: -- hint added as temporary R12 fix for 5573442
35468: QPLT.GROUP_AMOUNT,
35469: QPLT.LINE_QUANTITY, --2388011_new
35470: QPLT.SELECTED_VOLUME_ATTR -- 2388011_new
35471: ,Nvl(QPLT.ROUNDING_FACTOR,QPLT.SELLING_ROUNDING_FACTOR) ROUNDING_FACTOR --10093463
35472: FROM qp_npreq_lines_tmp QPLL,
35473: qp_npreq_ldets_tmp QPLT
35474: WHERE QPLL.LINE_TYPE_CODE = G_ORDER_LEVEL
35475: AND QPLT.LINE_INDEX = QPLL.LINE_INDEX
35476: AND QPLT.PRICING_STATUS_CODE = G_STATUS_NEW
35509: AND RELATIONSHIP_TYPE_CODE = G_SERVICE_LINE;
35510:
35511: -- Cursor for getting the unit price of the parent line ex: Oracle 8i line
35512: /*
35513: INDX,QP_PREQ_GRP.call_calculation_engine.l_get_price_parent_line_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
35514: */
35515: CURSOR l_get_price_parent_line_cur(l_line_index PLS_INTEGER) IS
35516: SELECT UNIT_PRICE,
35517: LINE_UNIT_PRICE, --fix bug 2776849, uom_service_pricing
35517: LINE_UNIT_PRICE, --fix bug 2776849, uom_service_pricing
35518: PRICED_QUANTITY,
35519: LINE_QUANTITY,
35520: LINE_UOM_CODE --fix bug 2776849, uom_service_pricing
35521: FROM qp_npreq_lines_tmp
35522: WHERE LINE_INDEX = l_line_index;
35523:
35524: /*
35525: INDX,lQP_PREQ_GRP.create_qualifier_from_list._check_modlist_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
36252: l_message := ' No refernce of parent item or parent price is null for percent-based pricing.';
36253: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
36254: engine_debug ('line_index: '||i.line_index);
36255: END IF;
36256: UPDATE qp_npreq_lines_tmp qplines
36257: SET
36258: PROCESSED_FLAG = G_PROCESSED,
36259: PERCENT_PRICE = p_cal_req_line_rec.PERCENT_PRICE -- still keep the percent_price
36260: WHERE qplines.LINE_INDEX = i.LINE_INDEX;
36362: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
36363: engine_debug ('In Line Update Information');
36364: END IF;
36365: FORALL M IN G_LINE_INDEX_TBL_C.FIRST.. G_LINE_INDEX_TBL_C.LAST
36366: UPDATE qp_npreq_lines_tmp qplines
36367: SET PRICING_STATUS_CODE = G_STATUS_UPDATED,
36368: PROCESSED_FLAG = G_PROCESSED,
36369: UNIT_PRICE = G_UNIT_PRICE_TBL_C(M),
36370: LINE_UNIT_PRICE = G_LINE_UNIT_PRICE_TBL_C(M), --shu_latest
36442: WHERE LINE_DETAIL_INDEX = G_LINE_DETAIL_INDEX_TBL_R(O);
36443: END IF;
36444:
36445: -- Update all GSA Violations at 1 shot
36446: --INDX,QP_PREQ_GRP.call_calculation_engine.call_calculation_engine_upd6,qp_npreq_lines_tmp_N1,LINE_INDEX,1
36447: IF (G_GSA_LINE_INDEX_TBL_C.COUNT > 0) THEN
36448: FORALL K IN G_GSA_LINE_INDEX_TBL_C.FIRST .. G_GSA_LINE_INDEX_TBL_C.LAST
36449: UPDATE qp_npreq_lines_tmp
36450: SET PRICING_STATUS_CODE = G_STATUS_GSA_VIOLATION,
36445: -- Update all GSA Violations at 1 shot
36446: --INDX,QP_PREQ_GRP.call_calculation_engine.call_calculation_engine_upd6,qp_npreq_lines_tmp_N1,LINE_INDEX,1
36447: IF (G_GSA_LINE_INDEX_TBL_C.COUNT > 0) THEN
36448: FORALL K IN G_GSA_LINE_INDEX_TBL_C.FIRST .. G_GSA_LINE_INDEX_TBL_C.LAST
36449: UPDATE qp_npreq_lines_tmp
36450: SET PRICING_STATUS_CODE = G_STATUS_GSA_VIOLATION,
36451: PRICING_STATUS_TEXT = l_status_text
36452: WHERE LINE_INDEX = G_GSA_LINE_INDEX_TBL_C(K);
36453: END IF;
36511: INDX,QP_PREQ_GRP.formula_processing.l_get_formula_id_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
36512: INDX,QP_PREQ_GRP.formula_processing.l_get_formula_id_cur,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
36513: INDX,QP_PREQ_GRP.formula_processing.l_get_formula_id_cur,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
36514:
36515: -- Full table scan on qp_npreq_lines_tmp
36516: */
36517: CURSOR l_get_formula_id_cur IS
36518:
36519: SELECT /*+ ORDERED USE_NL(b) index(b QP_PREQ_LDETS_TMP_N1) l_get_formula_id_cur */
36520: b.LINE_INDEX , b.PRICE_FORMULA_ID,a.PRICING_EFFECTIVE_DATE,b.CREATED_FROM_LIST_LINE_ID,
36521: b.OPERAND_CALCULATION_CODE , b.OPERAND_VALUE, b.CREATED_FROM_LIST_LINE_TYPE,b.LINE_DETAIL_TYPE_CODE,
36522: b.GROUP_QUANTITY,b.LINE_QUANTITY GROUP_AMOUNT
36523: FROM
36524: qp_npreq_lines_tmp a,
36525: qp_npreq_ldets_tmp b
36526: WHERE a.LINE_INDEX = b.LINE_INDEX
36527: AND b.PRICING_STATUS_CODE = G_STATUS_NEW
36528: AND b.PRICING_PHASE_ID = p_pricing_phase_id
36529: AND b.PRICE_FORMULA_ID IS NOT NULL;
36530:
36531: CURSOR l_get_list_price_cur(p_line_index PLS_INTEGER) IS
36532: SELECT UNIT_PRICE
36533: FROM qp_npreq_lines_tmp
36534: WHERE LINE_INDEX = p_line_index;
36535:
36536: l_req_line_attrs_rec QP_FORMULA_PRICE_CALC_PVT.req_line_attrs_rec;
36537: l_req_line_attrs_tbl QP_FORMULA_PRICE_CALC_PVT.req_line_attrs_tbl;
37062: AND LINE_INDEX = i.line_index
37063: AND PRICING_STATUS_CODE = G_STATUS_NEW;
37064:
37065: /*
37066: INDX,QP_PREQ_GRP.formula_processing.formula_processing_upd2.qp_npreq_lines_tmp_N1,LINE_INDEX,1
37067: */
37068: UPDATE qp_npreq_lines_tmp --upd2
37069: SET UNIT_PRICE = l_formula_based_price,
37070: PRICING_STATUS_CODE = G_STATUS_UPDATED,
37064:
37065: /*
37066: INDX,QP_PREQ_GRP.formula_processing.formula_processing_upd2.qp_npreq_lines_tmp_N1,LINE_INDEX,1
37067: */
37068: UPDATE qp_npreq_lines_tmp --upd2
37069: SET UNIT_PRICE = l_formula_based_price,
37070: PRICING_STATUS_CODE = G_STATUS_UPDATED,
37071: PRICING_STATUS_TEXT ='Priced by formula'
37072: WHERE LINE_INDEX = i.line_index;
37083: AND LINE_INDEX = i.line_index
37084: AND PRICING_STATUS_CODE = G_STATUS_NEW;
37085:
37086: /*
37087: INDX,QP_PREQ_GRP.formula_processing.formula_processing_upd4.qp_npreq_lines_tmp_N1,LINE_INDEX,1
37088: */
37089: UPDATE qp_npreq_lines_tmp --upd4
37090: SET PERCENT_PRICE = l_formula_based_price,
37091: PRICING_STATUS_CODE = G_STATUS_UPDATED,
37085:
37086: /*
37087: INDX,QP_PREQ_GRP.formula_processing.formula_processing_upd4.qp_npreq_lines_tmp_N1,LINE_INDEX,1
37088: */
37089: UPDATE qp_npreq_lines_tmp --upd4
37090: SET PERCENT_PRICE = l_formula_based_price,
37091: PRICING_STATUS_CODE = G_STATUS_UPDATED,
37092: PRICING_STATUS_TEXT ='Priced by formula'
37093: WHERE LINE_INDEX = i.line_index;
37189: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
37190: ENGINE_DEBUG('Truncate_Temp_tables: Java Engine not Installed ----------');
37191: END IF;
37192: --added by yangli for Java Engine project
37193: --Delete from qp_npreq_lines_tmp;
37194: --Delete from qp_npreq_line_attrs_tmp;
37195: --Delete From qp_npreq_ldets_tmp;
37196: --Delete From qp_npreq_rltd_lines_tmp;
37197: --Delete From QP_PREQ_QUAL_TMP;
37199: --Delete From QP_PREQ_LINE_ATTRS_FORMULA_TMP;
37200:
37201:
37202: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
37203: select count(*) into l_count from qp_npreq_lines_tmp;
37204: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
37205: engine_debug('qp_npreq_lines_tmp '||l_count);
37206: END IF;
37207: select count(*) into l_count from qp_npreq_line_attrs_tmp;
37201:
37202: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
37203: select count(*) into l_count from qp_npreq_lines_tmp;
37204: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
37205: engine_debug('qp_npreq_lines_tmp '||l_count);
37206: END IF;
37207: select count(*) into l_count from qp_npreq_line_attrs_tmp;
37208: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
37209: engine_debug('qp_npreq_line_attrs_tmp '||l_count);
37272: INDX,QP_PREQ_GRP.set_order_level_rltd.l_line_index_cur,- No Index Used -,NA,NA
37273: */
37274: CURSOR l_line_index_cur(p_freeze_override_flag VARCHAR2) is
37275: SELECT line_index
37276: FROM qp_npreq_lines_tmp
37277: WHERE pricing_status_code = G_STATUS_UPDATED
37278: AND (PRICE_FLAG = G_YES
37279: OR
37280: (PRICE_FLAG = G_PHASE
37690: IF NVL(FND_PROFILE.VALUE(QP_PREQ_GRP.G_BLIND_DISCOUNT),'N') = 'Y'
37691: THEN
37692: INSERT INTO qp_npreq_line_patrns_tmp(line_index, pattern_id, hash_key)
37693: SELECT line_index, -2, 'BLIND'
37694: FROM qp_npreq_lines_tmp;
37695:
37696: END IF;
37697: INSERT INTO qp_npreq_line_patrns_tmp(line_index, pattern_id, hash_key)
37698: SELECT line_index, -3, 'BLIND'
37695:
37696: END IF;
37697: INSERT INTO qp_npreq_line_patrns_tmp(line_index, pattern_id, hash_key)
37698: SELECT line_index, -3, 'BLIND'
37699: FROM qp_npreq_lines_tmp;
37700:
37701: INSERT INTO qp_npreq_line_patrns_tmp(line_index, pattern_id, hash_key)
37702: SELECT line_index, -1, 'NOEQUALS'
37703: FROM qp_npreq_lines_tmp;
37699: FROM qp_npreq_lines_tmp;
37700:
37701: INSERT INTO qp_npreq_line_patrns_tmp(line_index, pattern_id, hash_key)
37702: SELECT line_index, -1, 'NOEQUALS'
37703: FROM qp_npreq_lines_tmp;
37704:
37705: G_PATRNS_TWO_THREE_INSERTED := 'Y';
37706:
37707: END IF;
37768: FROM qp_list_header_phases qplhp,
37769: qp_secu_attr_groups_v atrgrps,
37770: --qp_attribute_groups atrgrps,
37771: qp_npreq_line_patrns_tmp qppattmp,
37772: qp_npreq_lines_tmp qplines,
37773: qp_pte_source_systems qprs
37774: --qp_price_req_sources_v qprs
37775: WHERE qplhp.pricing_phase_id = p_pricing_phase_id
37776: AND atrgrps.list_header_id = qplhp.list_header_id
37850: atrgrps.HEADER_LIMIT_EXISTS,
37851: atrgrps.LINE_LIMIT_EXISTS,
37852: atrgrps.descendents_quals_exist,
37853: atrgrps.PRICE_BREAK_TYPE_CODE
37854: FROM qp_npreq_lines_tmp qplines,
37855: qp_preq_patrn_qual_tmp intmdt,
37856: qp_npreq_line_patrns_tmp qppattmp,
37857: qp_attribute_groups atrgrps
37858: WHERE intmdt.pricing_phase_id = p_pricing_phase_id
37923: atrgrps.HEADER_LIMIT_EXISTS,
37924: atrgrps.LINE_LIMIT_EXISTS,
37925: atrgrps.descendents_quals_exist,
37926: atrgrps.PRICE_BREAK_TYPE_CODE
37927: FROM qp_npreq_lines_tmp qplines,
37928: qp_npreq_line_patrns_tmp qppattmp,
37929: qp_attribute_groups atrgrps
37930: WHERE atrgrps.pricing_phase_id = p_pricing_phase_id
37931: AND nvl(atrgrps.header_quals_exist_flag, 'N') = 'N'
38015: atrgrps.HEADER_LIMIT_EXISTS,
38016: atrgrps.LINE_LIMIT_EXISTS,
38017: atrgrps.descendents_quals_exist,
38018: atrgrps.PRICE_BREAK_TYPE_CODE
38019: FROM qp_npreq_lines_tmp qplines,
38020: qp_npreq_line_patrns_tmp qppattmp,
38021: qp_attribute_groups atrgrps,
38022: qp_pte_source_systems qprs
38023: WHERE atrgrps.list_line_id <> -1 -- Filter
38117: FROM qp_preq_patrn_qual_tmp intmdt,
38118: qp_secu_attr_groups_v atrgrps,
38119: --qp_attribute_groups atrgrps,
38120: qp_npreq_line_patrns_tmp qppattmp,
38121: qp_npreq_lines_tmp qplines
38122: WHERE intmdt.pricing_phase_id = p_pricing_phase_id
38123: AND atrgrps.list_header_id = intmdt.list_header_id
38124: AND intmdt.stage = G_STAGE_CACHE_MOD_LIN
38125: AND atrgrps.list_line_id = -1 -- Filter
38164: atrgrps_h.LIST_TYPE_CODE,
38165: atrgrps_h.ASK_FOR_FLAG,
38166: atrgrps_h.HEADER_LIMIT_EXISTS,
38167: atrgrps_l.LINE_LIMIT_EXISTS
38168: FROM qp_npreq_lines_tmp qplines,
38169: qp_npreq_line_patrns_tmp qppattmp_l,
38170: qp_attribute_groups atrgrps_l,
38171: qp_attribute_groups atrgrps_h,
38172: qp_npreq_line_patrns_tmp qppattmp_h,
40727: SELECT LINE_TMP.LINE_INDEX L_INDEX,
40728: ATTRS_TMP1.VALUE_FROM PRICE_LIST_HEADER_ID,
40729: LINE_TMP.LINE_QUANTITY LINE_QUANTITY,
40730: LINE_TMP.LINE_UOM_CODE LINE_UOM_CODE
40731: FROM QP_NPREQ_LINES_TMP LINE_TMP ,
40732: QP_NPREQ_LINE_ATTRS_TMP ATTRS_TMP,
40733: QP_NPREQ_LINE_ATTRS_TMP ATTRS_TMP1
40734: WHERE LINE_TMP.LINE_INDEX=ATTRS_TMP.LINE_INDEX
40735: AND LINE_TMP.LINE_INDEX=ATTRS_TMP1.LINE_INDEX
40935: WHERE PRICING_PHASE_ID = p_pricing_phase_id
40936: AND PRICING_STATUS_CODE = G_STATUS_NEW;
40937:
40938: /*
40939: INDX,QP_PREQ_GRP.price_request.l_get_list_price_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
40940: */
40941: CURSOR l_get_list_price_cur(p_line_index PLS_INTEGER) IS
40942: SELECT UNIT_PRICE,LINE_QUANTITY,PRICED_QUANTITY,PRICED_UOM_CODE,LINE_UOM_CODE,PRICING_EFFECTIVE_DATE
40943: FROM qp_npreq_lines_tmp
40939: INDX,QP_PREQ_GRP.price_request.l_get_list_price_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
40940: */
40941: CURSOR l_get_list_price_cur(p_line_index PLS_INTEGER) IS
40942: SELECT UNIT_PRICE,LINE_QUANTITY,PRICED_QUANTITY,PRICED_UOM_CODE,LINE_UOM_CODE,PRICING_EFFECTIVE_DATE
40943: FROM qp_npreq_lines_tmp
40944: WHERE LINE_INDEX = p_line_index;
40945: --FOR UPDATE OF ROUNDING_FACTOR;
40946:
40947: /*
40955: --From QP_PRICE_REQ_SOURCES
40956: From QP_PRICE_REQ_SOURCES_V /* yangli */
40957: Where REQUEST_TYPE_CODE
40958: In (SELECT REQUEST_TYPE_CODE
40959: FROM qp_npreq_lines_tmp));
40960:
40961: CURSOR get_request_type_code_cur IS -- shu, aso rounding
40962: SELECT REQUEST_TYPE_CODE
40963: FROM qp_npreq_lines_tmp
40959: FROM qp_npreq_lines_tmp));
40960:
40961: CURSOR get_request_type_code_cur IS -- shu, aso rounding
40962: SELECT REQUEST_TYPE_CODE
40963: FROM qp_npreq_lines_tmp
40964: WHERE rownum=1;
40965:
40966: /*
40967: INDX,QP_PREQ_GRP.price_request.debug_cur,- No Index Used -,NA,NA
40967: INDX,QP_PREQ_GRP.price_request.debug_cur,- No Index Used -,NA,NA
40968: */
40969: CURSOR debug_cur IS
40970: SELECT REQUEST_TYPE_CODE,LINE_INDEX,LINE_TYPE_CODE,PRICE_FLAG,PRICE_LIST_HEADER_ID,ROUNDING_FACTOR
40971: FROM qp_npreq_lines_tmp;
40972:
40973: /*
40974: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
40975: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
41090: */
41091: CURSOR order_amount_cur IS
41092: --SELECT nvl(SUM(UNIT_PRICE * PRICED_QUANTITY),0)
41093: select nvl(sum(nvl(line_unit_price * line_quantity, UNIT_PRICE * PRICED_QUANTITY)),0)
41094: FROM qp_npreq_lines_tmp
41095: WHERE charge_periodicity_code is NULL; -- added for recurring charges Bug # 4465168
41096:
41097: /*
41098: INDX,QP_PREQ_GRP.price_request.limit_trans_cur,QP_LIMIT_TRANSACTIONS_U1,PRICE_REQUEST_CODE,1
41147: --added by yangli for Java Engine changes
41148: CURSOR l_lines_info_cur
41149: is
41150: select line_index, unit_price, price_flag
41151: from qp_npreq_lines_tmp
41152: where QP_JAVA_ENGINE_UTIL_PUB.Java_Engine_Running = 'N'
41153: UNION ALL
41154: select line_index, unit_price, price_flag
41155: from qp_int_lines
41160: INDX,QP_PREQ_GRP.list_header_search.l_check_no_list_passed,- No Index Used-,NA,NA
41161: */
41162: CURSOR L_CHECK_NO_LIST_PASSED IS
41163: SELECT 'X'
41164: FROM qp_npreq_lines_tmp A
41165: WHERE PROCESSED_CODE IN (G_NO_LIST_PASSED,G_STS_LHS_NOT_FOUND)
41166: AND LINE_TYPE_CODE <> G_ORDER_LEVEL
41167: AND (instr(PROCESS_STATUS,'FREEGOOD')=0); -- bug 2693700
41168:
41170: INDX,QP_PREQ_GRP.list_header_search.l_check_secondary_search,- No Index Used-,NA,NA
41171: */
41172: CURSOR l_check_secondary_search IS
41173: SELECT 'X'
41174: FROM qp_npreq_lines_tmp
41175: WHERE PROCESSED_CODE = G_STS_LHS_NOT_FOUND
41176: AND LINE_TYPE_CODE <> G_ORDER_LEVEL
41177: AND (instr(PROCESS_STATUS,'FREEGOOD')=0); -- bug 2693700
41178:
41227:
41228: --KDURGASI changes for pattern based select modifiers
41229:
41230: IF qp_debug_util.IsTimeLogDebugOn THEN
41231: SELECT COUNT(*) INTO l_lines_cnt FROM qp_npreq_lines_tmp;
41232: END IF;
41233:
41234: qp_debug_util.addSummaryTimeLog('Event: ' || p_control_rec.PRICING_EVENT, 0,1,0);
41235: qp_debug_util.addSummaryTimeLog('Number of lines: ' || l_lines_cnt, 0,1,0);
41429: END IF;
41430: END IF; -- end if (p_line_tbl.COUNT > 0)
41431:
41432: ELSE --G_TEMP_TABLE_INSERT_FLAG = 'N', direct insert/performance path, shulin dbg 2437534
41433: --FOR cl IN (select line_index, unit_price, price_flag from qp_npreq_lines_tmp)
41434: FOR cl IN l_lines_info_cur --by yangli for Java Engine changes
41435: LOOP
41436: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
41437: engine_debug('In direct insert Loop / performance path');
41618: l_price_flag_yes_only := NULL; -- reset, default
41619: -- Check if atleast there is one line with price_flag = 'Y'
41620: -- shu, begin bug 2437534 fix
41621: IF l_price_flag_yes_only is null THEN
41622: --FOR cl IN (select unit_price, price_flag from qp_npreq_lines_tmp)
41623: FOR cl IN l_lines_info_cur
41624: LOOP
41625: IF cl.price_flag = G_YES OR (cl.unit_price is NULL and cl.price_flag = G_PHASE) then
41626: l_price_flag_yes_only := G_YES;
41636: BEGIN
41637: IF QP_JAVA_ENGINE_UTIL_PUB.Java_Engine_Running = 'N' THEN
41638: SELECT 1
41639: INTO l_price_flag_indx
41640: FROM qp_npreq_lines_tmp
41641: WHERE PRICE_FLAG IN (G_YES,G_PHASE)
41642: AND ROWNUM=1;
41643: --added by yangli for Java Engine
41644: ELSE
41712: -- because Populate_Temp_Tables was not called
41713: BEGIN
41714: select line_id
41715: into G_ORDER_ID
41716: from qp_npreq_lines_tmp
41717: where line_type_code = QP_PREQ_GRP.G_ORDER_LEVEL;
41718: EXCEPTION
41719: WHEN NO_DATA_FOUND THEN
41720: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
41743: QP_PREQ_GRP.engine_debug('ignore_pricing : Updating non-pricable lines 2');
41744: --print_tmp_tabs();
41745: END IF;
41746: FORALL i in 1 .. l_line_index_tbl.COUNT
41747: UPDATE qp_npreq_lines_tmp
41748: SET
41749: price_flag = G_NO,
41750: PRICE_LIST_HEADER_ID = l_price_list_header_id_tbl(i),
41751: LINE_UNIT_PRICE = 0,
41958: engine_debug ('G_PTE_CODE: '||G_PTE_CODE);
41959:
41960: SELECT MIN(line_index), MAX(line_index)
41961: INTO G_MIN_LINE_INDEX,G_MAX_LINE_INDEX
41962: FROM QP_NPREQ_LINES_TMP;
41963:
41964: engine_debug ('G_MIN_LINE_INDEX: '||G_MIN_LINE_INDEX);
41965: engine_debug ('G_MAX_LINE_INDEX: '||G_MAX_LINE_INDEX);
41966:
42238: -- 3773652
42239: -- re-run Evaluate_Between after UOM conversion to check PLL really qualifies
42240: begin
42241: select 'x' into l_dummy
42242: from qp_npreq_lines_tmp
42243: where LINE_UOM_CODE <> nvl(PRICED_UOM_CODE, LINE_UOM_CODE)
42244: and (PRICE_FLAG = G_YES
42245: or
42246: PRICE_FLAG = G_PHASE and l_freeze_override_flag = G_YES)
42385:
42386: IF (G_PRICE_PHASE_FLAG=FALSE) THEN
42387:
42388: --[julin/pricebook] added hint
42389: UPDATE qp_npreq_lines_tmp a
42390: SET (price_list_header_id,validated_flag) =
42391: (SELECT /*+ index(b QP_PREQ_LINE_ATTRS_TMP_N2) */ distinct qp_number.canonical_to_number(value_from),validated_flag
42392: FROM qp_npreq_line_attrs_tmp b
42393: WHERE a.line_index = b.line_index
42824: engine_debug ('Total order amount = ' || v_order_amount);
42825: END IF;
42826:
42827: /*
42828: INDX,QP_PREQ_GRP.price_request.price_request_upd1,qp_npreq_lines_tmp_N2,LINE_TYPE_CODE,1
42829: */
42830: UPDATE qp_npreq_lines_tmp
42831: SET UNIT_PRICE = v_order_amount,
42832: PRICED_QUANTITY = 1
42826:
42827: /*
42828: INDX,QP_PREQ_GRP.price_request.price_request_upd1,qp_npreq_lines_tmp_N2,LINE_TYPE_CODE,1
42829: */
42830: UPDATE qp_npreq_lines_tmp
42831: SET UNIT_PRICE = v_order_amount,
42832: PRICED_QUANTITY = 1
42833: WHERE LINE_TYPE_CODE = 'ORDER';
42834: