[Home] [Help]
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
3340: /*
3341: INDX,QP_PREQ_GRP.set_status_code.set_status_code_upd2,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
3342: */
3343: -- shulin fix bug1745788, widen substr to 2000
3344: UPDATE qp_npreq_ldets_tmp --upd2
3345: SET PRICING_STATUS_CODE = p_status_code,
3340: /*
3341: INDX,QP_PREQ_GRP.set_status_code.set_status_code_upd2,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
3342: */
3343: -- shulin fix bug1745788, widen substr to 2000
3344: UPDATE qp_npreq_ldets_tmp --upd2
3345: SET PRICING_STATUS_CODE = p_status_code,
3346: PRICING_STATUS_TEXT =
3347: substr(pricing_status_code||'+'||p_status_code||'+'||p_status_text,1,2000)
3348: WHERE LINE_INDEX = p_line_index
3416: END SET_PROCESSED_CODE;
3417:
3418: /*+----------------------------------------------------
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
5334: x_status_text OUT NOCOPY VARCHAR2)
5335: AS
5336: l_routine VARCHAR2(240):= 'Routine:QP_PREQ_GRP.INSERT_LDETS';
5337: BEGIN
5338: INSERT INTO qp_npreq_ldets_tmp
5339: (LINE_DETAIL_INDEX,
5340: LINE_DETAIL_TYPE_CODE,
5341: PRICE_BREAK_TYPE_CODE,
5342: LINE_INDEX,
5504:
5505: BEGIN
5506:
5507: FORALL i in p_line_index.FIRST..p_line_index.LAST
5508: INSERT INTO qp_npreq_ldets_tmp
5509: (LINE_DETAIL_INDEX,
5510: LINE_DETAIL_TYPE_CODE,
5511: PRICE_BREAK_TYPE_CODE,
5512: LINE_INDEX,
5622:
5623: BEGIN
5624:
5625: FORALL i in p_line_index.FIRST..p_line_index.LAST
5626: INSERT INTO qp_npreq_ldets_tmp
5627: (LINE_DETAIL_INDEX,
5628: LINE_DETAIL_TYPE_CODE,
5629: PRICE_BREAK_TYPE_CODE,
5630: LINE_INDEX,
6025: END IF;
6026: --added by yangli for Java Engine project
6027:
6028: FORALL i in p_line_index.FIRST..p_line_index.LAST
6029: INSERT INTO qp_npreq_ldets_tmp
6030: (LINE_DETAIL_INDEX,
6031: LINE_DETAIL_TYPE_CODE,
6032: PRICE_BREAK_TYPE_CODE,
6033: LINE_INDEX,
6232:
6233: BEGIN
6234:
6235: FORALL i in p_line_index.FIRST..p_line_index.LAST
6236: INSERT INTO qp_npreq_ldets_tmp
6237: (LINE_DETAIL_INDEX,
6238: LINE_DETAIL_TYPE_CODE,
6239: PRICE_BREAK_TYPE_CODE,
6240: LINE_INDEX,
6862: END IF;
6863:
6864: --added by yangli for Java Engine project
6865: FORALL i in p_line_index.FIRST..p_line_index.LAST
6866: INSERT INTO qp_npreq_ldets_tmp
6867: (LINE_DETAIL_INDEX,
6868: LINE_DETAIL_TYPE_CODE,
6869: PRICE_BREAK_TYPE_CODE,
6870: LINE_INDEX,
7786: l_routine VARCHAR2(40) := 'Delete_Duplicate_List_Lines';
7787:
7788: CURSOR get_duplicate_list_lines_count IS
7789: SELECT count(*)
7790: FROM qp_npreq_ldets_tmp
7791: WHERE pricing_status_code = G_STATUS_NEW
7792: AND pricing_phase_id = p_pricing_phase_id
7793: GROUP BY line_index,created_from_list_line_id
7794: HAVING count(*) > 1;
7809: engine_debug('Duplicate List Lines Count : ' || l_count);
7810: END IF;
7811:
7812: IF (l_count > 0) THEN
7813: UPDATE qp_npreq_ldets_tmp a
7814: SET pricing_status_code = 'DUPLICATE_LIST_LINE'
7815: WHERE pricing_status_code = G_STATUS_NEW
7816: AND pricing_phase_id = p_pricing_phase_id
7817: AND rowid not in (SELECT min(rowid)
7814: SET pricing_status_code = 'DUPLICATE_LIST_LINE'
7815: WHERE pricing_status_code = G_STATUS_NEW
7816: AND pricing_phase_id = p_pricing_phase_id
7817: AND rowid not in (SELECT min(rowid)
7818: FROM qp_npreq_ldets_tmp
7819: WHERE pricing_status_code = G_STATUS_NEW
7820: AND pricing_phase_id = p_pricing_phase_id
7821: GROUP BY line_index,created_from_list_line_id);
7822: END IF;
7934: -- Delete lines which are actually ask_for in setup , but are not asked for in the Order
7935: -- Because Engine selects these lines irrespective of their ask_for_flag
7936: -- Index Certificate
7937: /*
7938: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
7939: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
7940: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
7941: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
7942: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
7935: -- Because Engine selects these lines irrespective of their ask_for_flag
7936: -- Index Certificate
7937: /*
7938: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
7939: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
7940: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
7941: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
7942: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
7943: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_line_attrs_tmp_N2,CONTEXT,3
7936: -- Index Certificate
7937: /*
7938: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
7939: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
7940: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
7941: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
7942: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
7943: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_line_attrs_tmp_N2,CONTEXT,3
7944: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE,4
7951: l_pricing_status_code := QP_PREQ_GRP.G_STATUS_INCOMP_LOGIC;
7952: END IF;
7953:
7954:
7955: UPDATE qp_npreq_ldets_tmp a --upd1
7956: SET PRICING_STATUS_CODE = l_pricing_status_code
7957: WHERE a.ASK_FOR_FLAG = QP_PREQ_GRP.G_YES
7958: AND a.PRICING_PHASE_ID = p_pricing_phase_id
7959: AND a.LINE_INDEX = p_line_index
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:
9199: INDX,QP_PREQ_GRP.populate_output.l_qual_cur,qp_npreq_line_attrs_tmp_N3,LINE_DETAIL_INDEX,1
9200: INDX,QP_PREQ_GRP.populate_output.l_qual_cur,qp_npreq_line_attrs_tmp_N3,ATTRIBUTE_TYPE,2
9201: */
9209: QPLAT.COMPARISON_OPERATOR_TYPE_CODE,
9210: QPLAT.VALIDATED_FLAG,
9211: QPLAT.PRICING_STATUS_CODE,
9212: QPLAT.PRICING_STATUS_TEXT
9213: FROM qp_npreq_ldets_tmp QPLD ,
9214: qp_npreq_line_attrs_tmp QPLAT
9215: WHERE QPLD.LINE_DETAIL_INDEX = QPLAT.LINE_DETAIL_INDEX
9216: AND QPLD.PRICING_STATUS_CODE = G_STATUS_NEW
9217: AND QPLAT.ATTRIBUTE_TYPE = 'QUALIFIER';
9217: AND QPLAT.ATTRIBUTE_TYPE = 'QUALIFIER';
9218: --AND QPLAT.PRICING_STATUS_CODE = G_STATUS_NEW;
9219:
9220: /*
9221: INDX,QP_PREQ_GRP.populate_output.l_pricing_attr_cur,qp_npreq_ldets_tmp_N4,PRICING_STATUS_CODE,1
9222:
9223: INDX,QP_PREQ_GRP.populate_output.l_pricing_attr_cur,qp_npreq_line_attrs_tmp_N3,LINE_DETAIL_INDEX,1
9224: INDX,QP_PREQ_GRP.populate_output.l_pricing_attr_cur,qp_npreq_line_attrs_tmp_N3,ATTRIBUTE_TYPE,2
9225: */
9231: QPLAT_PRICING.COMPARISON_OPERATOR_TYPE_CODE,
9232: QPLAT_PRICING.LINE_DETAIL_INDEX,
9233: QPLAT_PRICING.LINE_INDEX,
9234: QPLAT_PRICING.VALIDATED_FLAG
9235: FROM qp_npreq_ldets_tmp QPLD ,
9236: qp_npreq_line_attrs_tmp QPLAT_PRICING
9237: WHERE QPLD.LINE_DETAIL_INDEX = QPLAT_PRICING.LINE_DETAIL_INDEX
9238: AND QPLD.PRICING_STATUS_CODE = G_STATUS_NEW
9239: AND QPLAT_PRICING.ATTRIBUTE_TYPE IN ('PRICING','PRODUCT');
9241:
9242: /*
9243: INDX,QP_PREQ_GRP.populate_output.l_ldets_cur,QP_LIST_LINES_PK,LIST_LINE_ID,1
9244:
9245: INDX,QP_PREQ_GRP.populate_output.l_ldets_cur,qp_npreq_ldets_tmp_N4,PRICING_STATUS_CODE,1
9246:
9247: INDX,QP_PREQ_GRP.populate_output.l_ldets_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
9248: */
9249: CURSOR l_ldets_cur IS
9309: b.COMMENTS,
9310: b.break_uom_code, /* proration */
9311: b.break_uom_context, /* proration */
9312: b.break_uom_attribute /* proration */
9313: FROM qp_npreq_ldets_tmp a,
9314: QP_LIST_LINES b
9315: WHERE a.CREATED_FROM_LIST_LINE_ID = b.LIST_LINE_ID
9316: AND a.PRICING_STATUS_CODE = G_STATUS_NEW;
9317:
10093: INDX,QP_PREQ_GRP.eliminate_multiple_hierarchies.l_get_multiple_exclude_cur,qp_npreq_line_attrs_tmp_N5,PRICING_STATUS_CODE,3
10094: INDX,QP_PREQ_GRP.eliminate_multiple_hierarchies.l_get_multiple_exclude_cur,qp_npreq_line_attrs_tmp_N5,LINE_INDEX,4
10095: INDX,QP_PREQ_GRP.eliminate_multiple_hierarchies.l_get_multiple_exclude_cur,qp_npreq_line_attrs_tmp_N5,COMPARISON_OPERATOR_CODE,5
10096:
10097: INDX,QP_PREQ_GRP.eliminate_multiple_hierarchies.l_get_multiple_exclude_cur,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
10098:
10099: */
10100:
10101: --Pricing Phase Change
10102: CURSOR l_get_multiple_exclude_cur IS
10103: SELECT /*+ ORDERED USE_NL(b) index(a qp_preq_line_attrs_tmp_N5) l_get_multiple_exclude_cur */
10104: distinct a.line_index , a.list_header_id , a.list_line_id , a.grouping_number grouping_no,
10105: a.context , a.attribute , a.setup_value_from
10106: FROM qp_npreq_line_attrs_tmp a , qp_npreq_ldets_tmp b
10107: WHERE
10108: a.comparison_operator_type_code = 'NOT ='
10109: AND a.attribute_type IN (G_PRICING_TYPE,G_QUALIFIER_TYPE)
10110: AND a.pricing_status_code = G_STATUS_NEW
10228: AND pricing_status_code = G_STATUS_NEW -- Expect an issue because of this join condition
10229: AND attribute_type IN (G_PRICING_TYPE,G_QUALIFIER_TYPE);
10230:
10231: /* FORALL j IN l_del_list_line_tbl.FIRST .. l_del_list_line_tbl.LAST
10232: UPDATE qp_npreq_ldets_tmp
10233: SET pricing_status_code = G_DELETED_EXCLUDER
10234: WHERE created_from_list_header_id = l_del_list_header_tbl(j)
10235: AND created_from_list_line_id = l_del_list_line_tbl(j)
10236: AND line_index = l_del_line_index_tbl(j)
10286: select /*+ ORDERED USE_NL(c a ) index(a qp_pricing_attributes_n2) line_grp_cur */
10287: b.created_from_list_line_id list_line_id , c.context , c.attribute ,
10288: sum(qp_number.canonical_to_number(c.value_from)) group_value
10289: from
10290: qp_npreq_ldets_tmp b , qp_npreq_line_attrs_tmp c , qp_pricing_attributes a
10291: where
10292: b.modifier_level_code = G_LINE_GROUP
10293: and b.pricing_phase_id = p_pricing_phase_id
10294: and c.pricing_status_code = G_STATUS_UNCHANGED
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:
10380:
10381: /*
10382: INDX,QP_PREQ_GRP.process_line_group.upd2,-No Index Used-,NA,NA
10383: */
10384: UPDATE qp_npreq_ldets_tmp --upd2
10385: SET LINE_QUANTITY = l_group_quantity, -- 2388011_latest, shu fix pbh_grp_amt
10386: GROUP_QUANTITY = l_group_quantity, -- 2388011_latest, shu fix pbh_grp_amt
10387: SELECTED_VOLUME_ATTR = G_QUANTITY -- 2388011_latest
10388: WHERE CREATED_FROM_LIST_LINE_ID = j.list_line_id;
10431: INDX,QP_PREQ_GRP11.process_line_group.upd4,-No Index Used-,NA,NA
10432: */
10433: IF (G_LINE_INDEX_TBL_G.COUNT > 0) THEN
10434: FORALL I IN G_LINE_INDEX_TBL_G.FIRST .. G_LINE_INDEX_TBL_G.LAST
10435: UPDATE qp_npreq_ldets_tmp --upd4
10436: SET GROUP_AMOUNT = G_GROUP_AMOUNT_PER_UNIT_TBL_G(I),
10437: LINE_QUANTITY = l_group_amount, -- 2388011_latest
10438: SELECTED_VOLUME_ATTR = G_AMOUNT -- 2388011_latest
10439: WHERE CREATED_FROM_LIST_LINE_ID = j.list_line_id
10477: BEGIN
10478:
10479: IF (p_line_detail_index IS NOT NULL) THEN
10480: /*
10481: INDX,QP_PREQ_GRP.is_line_group.is_line_group_sel1,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
10482: */
10483: SELECT 'X'
10484: INTO l_dummy
10485: FROM qp_npreq_ldets_tmp
10481: INDX,QP_PREQ_GRP.is_line_group.is_line_group_sel1,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
10482: */
10483: SELECT 'X'
10484: INTO l_dummy
10485: FROM qp_npreq_ldets_tmp
10486: WHERE line_detail_index = p_line_detail_index
10487: AND modifier_level_code = G_LINE_GROUP;
10488: ELSE
10489: /*
10486: WHERE line_detail_index = p_line_detail_index
10487: AND modifier_level_code = G_LINE_GROUP;
10488: ELSE
10489: /*
10490: INDX,QP_PREQ_GRP.is_line_group.is_line_group_sel2,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
10491: */
10492: SELECT 'X'
10493: INTO l_dummy
10494: FROM qp_npreq_ldets_tmp
10490: INDX,QP_PREQ_GRP.is_line_group.is_line_group_sel2,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
10491: */
10492: SELECT 'X'
10493: INTO l_dummy
10494: FROM qp_npreq_ldets_tmp
10495: WHERE line_index = p_line_index
10496: AND modifier_level_code = G_LINE_GROUP;
10497: END IF;
10498:
10625: /*
10626: INDX,QP_PREQ_GRP.update_request_line_status.update_request_line_status_upd1,-No Index Used-,NA,NA
10627: */
10628: /*
10629: INDX,QP_PREQ_GRP.update_request_line_status.sel1,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
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:
10626: INDX,QP_PREQ_GRP.update_request_line_status.update_request_line_status_upd1,-No Index Used-,NA,NA
10627: */
10628: /*
10629: INDX,QP_PREQ_GRP.update_request_line_status.sel1,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
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
10627: */
10628: /*
10629: INDX,QP_PREQ_GRP.update_request_line_status.sel1,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
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
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
10639: WHERE a.line_index = b.line_index
10640: AND b.pricing_status_code = G_STATUS_NEW
10641: AND b.pricing_phase_id = G_PRICE_LIST_PHASE_ID)
10642: AND line_type_code <> G_ORDER_LEVEL;
10640: AND b.pricing_status_code = G_STATUS_NEW
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
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
10650: AND b.pricing_status_code = G_STATUS_NEW
10651: AND b.pricing_phase_id = G_PRICE_LIST_PHASE_ID)
10652: AND line_type_code <> G_ORDER_LEVEL
10724:
10725: ELSE
10726:
10727: /*
10728: INDX,QP_PREQ_GRP.update_request_line_status.sel3,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
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:
10725: ELSE
10726:
10727: /*
10728: INDX,QP_PREQ_GRP.update_request_line_status.sel3,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
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
10726:
10727: /*
10728: INDX,QP_PREQ_GRP.update_request_line_status.sel3,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
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
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
10738: AND b.pricing_status_code = G_STATUS_NEW
10739: AND b.pricing_phase_id = G_PRICE_LIST_PHASE_ID)
10740: AND line_type_code <> G_ORDER_LEVEL
10772: INDX,QP_PREQ_GRP.Evaluate_Between.l_pric_between_cur,qp_npreq_line_attrs_tmp_N5,LINE_INDEX,4
10773: INDX,QP_PREQ_GRP.Evaluate_Between.l_pric_between_cur,qp_npreq_line_attrs_tmp_N5,COMPARISON_OPERATOR_CODE,5
10774: INDX,QP_PREQ_GRP.Evaluate_Between.l_pric_between_cur,qp_npreq_line_attrs_tmp_N5,DATATYPE,6
10775:
10776: INDX,QP_PREQ_GRP.Evaluate_Between.l_pric_between_cur,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
10777:
10778: */
10779:
10780: CURSOR l_pric_between_cur(p_datatype VARCHAR2) IS
10790: pricing.product_uom_code,
10791: pricing.attribute,
10792: pricing.context,
10793: pricing.attribute_type
10794: FROM qp_npreq_line_attrs_tmp pricing,qp_npreq_ldets_tmp ldets
10795: WHERE pricing.comparison_operator_type_code = G_OPERATOR_BETWEEN
10796: AND pricing.datatype = p_datatype
10797: AND pricing.pricing_phase_id = G_PRICE_LIST_PHASE_ID
10798: AND pricing.attribute_type IN (G_PRICING_TYPE,G_QUALIFIER_TYPE)
10809: INDX,QP_PREQ_GRP.Evaluate_Between.l_mod_between_cur,qp_npreq_line_attrs_tmp_N5,LINE_INDEX,4
10810: INDX,QP_PREQ_GRP.Evaluate_Between.l_mod_between_cur,qp_npreq_line_attrs_tmp_N5,COMPARISON_OPERATOR_CODE,5
10811: INDX,QP_PREQ_GRP.Evaluate_Between.l_mod_between_cur,qp_npreq_line_attrs_tmp_N5,DATATYPE,6
10812:
10813: INDX,QP_PREQ_GRP.Evaluate_Between.l_pric_between_cur,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
10814:
10815: */
10816:
10817: CURSOR l_mod_between_cur(p_datatype VARCHAR2) IS
10826: pricing.product_uom_code,
10827: pricing.attribute,
10828: pricing.context,
10829: pricing.attribute_type
10830: FROM qp_npreq_line_attrs_tmp pricing,qp_npreq_ldets_tmp ldets
10831: WHERE pricing.comparison_operator_type_code = G_OPERATOR_BETWEEN
10832: AND pricing.datatype = p_datatype
10833: AND pricing.pricing_phase_id = p_pricing_phase_id
10834: AND pricing.attribute_type = G_PRICING_TYPE
10845: INDX,QP_PREQ_GRP.Evaluate_Between.l_pric_character_between_cur,qp_npreq_line_attrs_tmp_N5,LINE_INDEX,4
10846: INDX,QP_PREQ_GRP.Evaluate_Between.l_pric_character_between_cur,qp_npreq_line_attrs_tmp_N5,COMPARISON_OPERATOR_CODE,5
10847: INDX,QP_PREQ_GRP.Evaluate_Between.l_pric_character_between_cur,qp_npreq_line_attrs_tmp_N5,DATATYPE,6
10848:
10849: INDX,QP_PREQ_GRP.Evaluate_Between.l_pric_character_between_cur,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
10850:
10851: */
10852:
10853: CURSOR l_pric_character_between_cur IS
10860: b.product_uom_code,
10861: b.attribute,
10862: b.context,
10863: b.attribute_type
10864: FROM qp_npreq_line_attrs_tmp b,qp_npreq_ldets_tmp c
10865: WHERE comparison_operator_type_code = G_OPERATOR_BETWEEN
10866: AND b.pricing_phase_id = G_PRICE_LIST_PHASE_ID
10867: AND datatype in (G_DATE_X,G_DATE_Y,G_VARCHAR)
10868: AND attribute_type IN (G_PRICING_TYPE,G_QUALIFIER_TYPE)
10880: INDX,QP_PREQ_GRP.Evaluate_Between.l_mod_character_between_cur,qp_npreq_line_attrs_tmp_N5,LINE_INDEX,4
10881: INDX,QP_PREQ_GRP.Evaluate_Between.l_mod_character_between_cur,qp_npreq_line_attrs_tmp_N5,COMPARISON_OPERATOR_CODE,5
10882: INDX,QP_PREQ_GRP.Evaluate_Between.l_mod_character_between_cur,qp_npreq_line_attrs_tmp_N5,DATATYPE,6
10883:
10884: INDX,QP_PREQ_GRP.Evaluate_Between.l_pric_character_between_cur,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
10885:
10886: */
10887:
10888: CURSOR l_mod_character_between_cur IS
10895: b.product_uom_code,
10896: b.attribute,
10897: b.context,
10898: b.attribute_type
10899: FROM qp_npreq_line_attrs_tmp b,qp_npreq_ldets_tmp c
10900: WHERE comparison_operator_type_code = G_OPERATOR_BETWEEN
10901: AND b.pricing_phase_id = p_pricing_phase_id
10902: AND datatype in (G_DATE_X,G_DATE_Y,G_VARCHAR)
10903: AND attribute_type = G_PRICING_TYPE
11113:
11114: IF (I.group_quantity IS NOT NULL and I.group_quantity <> l_value_from_num and l_value_from_num IS NOT NULL
11115: and p_line_group_flag IS NOT NULL) THEN
11116: /*
11117: INDX,QP_PREQ_GRP.evaluate_between.evaluate_between_upd1,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
11118: */
11119: UPDATE qp_npreq_ldets_tmp
11120: SET GROUP_QUANTITY = l_value_from_num
11121: WHERE LINE_INDEX = i.line_index
11115: and p_line_group_flag IS NOT NULL) THEN
11116: /*
11117: INDX,QP_PREQ_GRP.evaluate_between.evaluate_between_upd1,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
11118: */
11119: UPDATE qp_npreq_ldets_tmp
11120: SET GROUP_QUANTITY = l_value_from_num
11121: WHERE LINE_INDEX = i.line_index
11122: AND LINE_DETAIL_INDEX = i.line_detail_index;
11123: END IF;
11133:
11134: IF (I.group_amount IS NOT NULL and l_value_from_num IS NOT NULL -- shu
11135: and p_line_group_flag IS NOT NULL) THEN
11136: /*
11137: INDX,QP_PREQ_GRP.evaluate_between.evaluate_between_upd2,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
11138: */
11139:
11140:
11141: -- begin shu, fix group of lines lumpsum based on ITEM_AMOUNT
11163: QP_PREQ_GRP.engine_debug('group_value: '||l_group_amount);
11164: END IF;
11165: -- end shu fix
11166:
11167: UPDATE qp_npreq_ldets_tmp
11168: SET GROUP_AMOUNT = l_group_amount -- shu
11169: WHERE LINE_INDEX = i.line_index
11170: AND LINE_DETAIL_INDEX = i.line_detail_index;
11171:
11179: QP_PREQ_GRP.engine_debug('Line Index: ' || i.line_index || ' LD Index : ' || i.line_detail_index ||
11180: ' '||l_value_from_num);
11181: END IF;
11182: /*
11183: INDX,QP_PREQ_GRP.evaluate_between.evaluate_between_upd3,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
11184: */
11185: UPDATE qp_npreq_ldets_tmp
11186: SET LINE_QUANTITY = l_value_from_num
11187: WHERE LINE_INDEX = i.line_index
11181: END IF;
11182: /*
11183: INDX,QP_PREQ_GRP.evaluate_between.evaluate_between_upd3,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
11184: */
11185: UPDATE qp_npreq_ldets_tmp
11186: SET LINE_QUANTITY = l_value_from_num
11187: WHERE LINE_INDEX = i.line_index
11188: AND LINE_DETAIL_INDEX = i.line_detail_index;
11189: END IF;
11279:
11280: IF (I.group_quantity IS NOT NULL and I.group_quantity <> l_value_from_num and l_value_from_num IS NOT NULL
11281: and p_line_group_flag IS NOT NULL) THEN
11282: /*
11283: INDX,QP_PREQ_GRP.evaluate_between.evaluate_between_upd1,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
11284: */
11285: UPDATE qp_npreq_ldets_tmp
11286: SET GROUP_QUANTITY = l_value_from_num, --2388011_new
11287: SELECTED_VOLUME_ATTR = l_selected_volume_attr -- 2388011_new
11281: and p_line_group_flag IS NOT NULL) THEN
11282: /*
11283: INDX,QP_PREQ_GRP.evaluate_between.evaluate_between_upd1,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
11284: */
11285: UPDATE qp_npreq_ldets_tmp
11286: SET GROUP_QUANTITY = l_value_from_num, --2388011_new
11287: SELECTED_VOLUME_ATTR = l_selected_volume_attr -- 2388011_new
11288: WHERE LINE_INDEX = i.line_index
11289: AND LINE_DETAIL_INDEX = i.line_detail_index;
11300: END IF;
11301: IF (I.group_amount IS NOT NULL and l_value_from_num IS NOT NULL
11302: and p_line_group_flag IS NOT NULL) THEN
11303: /*
11304: INDX,QP_PREQ_GRP.evaluate_between.evaluate_between_upd2,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
11305: */
11306:
11307:
11308: -- [julin/5085310]
11347: QP_PREQ_GRP.engine_debug('i.group_amount: '||i.group_amount);
11348: QP_PREQ_GRP.engine_debug('group_value: '||l_group_amount);
11349: END IF;
11350:
11351: UPDATE qp_npreq_ldets_tmp
11352: SET GROUP_AMOUNT = l_group_amount, -- 2388011_new, fix group of lines lumpsum based on ITEM_AMOUNT
11353: SELECTED_VOLUME_ATTR = l_selected_volume_attr -- 2388011_new
11354: WHERE LINE_INDEX = i.line_index
11355: AND LINE_DETAIL_INDEX = i.line_detail_index;
11363: QP_PREQ_GRP.engine_debug('Line Index: ' || i.line_index || ' LD Index : ' || i.line_detail_index ||
11364: ' '||l_value_from_num);
11365: END IF;
11366: /*
11367: INDX,QP_PREQ_GRP.evaluate_between.evaluate_between_upd3,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
11368: */
11369: UPDATE qp_npreq_ldets_tmp
11370: SET LINE_QUANTITY = l_value_from_num, --2388011_new
11371: SELECTED_VOLUME_ATTR = l_selected_volume_attr --2388011_new
11365: END IF;
11366: /*
11367: INDX,QP_PREQ_GRP.evaluate_between.evaluate_between_upd3,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
11368: */
11369: UPDATE qp_npreq_ldets_tmp
11370: SET LINE_QUANTITY = l_value_from_num, --2388011_new
11371: SELECTED_VOLUME_ATTR = l_selected_volume_attr --2388011_new
11372: WHERE LINE_INDEX = i.line_index
11373: AND LINE_DETAIL_INDEX = i.line_detail_index;
11383: -- attribute BETWEEN fails
11384: FORALL I IN 1 .. l_line_detail_tbl.COUNT
11385:
11386: /*
11387: INDX,QP_PREQ_GRP.evaluate_between.upd4,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
11388: */
11389: UPDATE qp_npreq_ldets_tmp --upd4
11390: SET PRICING_STATUS_CODE = G_DELETED_BETWEEN
11391: WHERE LINE_DETAIL_INDEX = l_line_detail_tbl(i);
11385:
11386: /*
11387: INDX,QP_PREQ_GRP.evaluate_between.upd4,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
11388: */
11389: UPDATE qp_npreq_ldets_tmp --upd4
11390: SET PRICING_STATUS_CODE = G_DELETED_BETWEEN
11391: WHERE LINE_DETAIL_INDEX = l_line_detail_tbl(i);
11392:
11393: EXCEPTION
11945: engine_debug('UOM p_group_quantity :'||p_group_quantity);
11946:
11947: END IF;
11948: /*
11949: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
11950: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
11951: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
11952: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
11953: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
11946:
11947: END IF;
11948: /*
11949: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
11950: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
11951: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
11952: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
11953: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
11954: */
11947: END IF;
11948: /*
11949: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
11950: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
11951: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
11952: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
11953: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
11954: */
11955:
11948: /*
11949: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
11950: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
11951: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
11952: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
11953: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
11954: */
11955:
11956: UPDATE qp_npreq_ldets_tmp --upd1
11949: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
11950: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
11951: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
11952: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
11953: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
11954: */
11955:
11956: UPDATE qp_npreq_ldets_tmp --upd1
11957: SET GROUP_QUANTITY = l_quantity, --2388011_new
11952: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
11953: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
11954: */
11955:
11956: UPDATE qp_npreq_ldets_tmp --upd1
11957: SET GROUP_QUANTITY = l_quantity, --2388011_new
11958: SELECTED_VOLUME_ATTR = l_selected_volume_attr -- 2388011_new, ASK RAVI, commented out??
11959: WHERE CREATED_FROM_LIST_HEADER_ID = p_list_header_id
11960: AND PRICING_PHASE_ID = p_pricing_phase_id
12031: -- This LINE_QUANTITY is column is needed for knowing the right value for
12032: -- PBH based on modifier level code 'LINE' for calculation purposes
12033:
12034: /*
12035: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
12036: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
12037: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
12038: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
12039: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
12032: -- PBH based on modifier level code 'LINE' for calculation purposes
12033:
12034: /*
12035: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
12036: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
12037: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
12038: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
12039: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
12040: */
12033:
12034: /*
12035: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
12036: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
12037: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
12038: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
12039: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
12040: */
12041:
12034: /*
12035: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
12036: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
12037: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
12038: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
12039: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
12040: */
12041:
12042: UPDATE qp_npreq_ldets_tmp --upd2
12035: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
12036: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
12037: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
12038: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
12039: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
12040: */
12041:
12042: UPDATE qp_npreq_ldets_tmp --upd2
12043: SET LINE_QUANTITY = l_quantity, --2388011_new
12038: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
12039: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
12040: */
12041:
12042: UPDATE qp_npreq_ldets_tmp --upd2
12043: SET LINE_QUANTITY = l_quantity, --2388011_new
12044: SELECTED_VOLUME_ATTR = l_selected_volume_attr -- 2388011_new
12045: WHERE CREATED_FROM_LIST_HEADER_ID = p_list_header_id
12046: AND PRICING_PHASE_ID = p_pricing_phase_id
12101:
12102: if l_first_time_flag = 'Y' then
12103: select break_uom_code, break_uom_context, break_uom_attribute
12104: into l_break_uom_code, l_break_uom_context, l_break_uom_attribute
12105: from qp_npreq_ldets_tmp
12106: where line_detail_index = p_line_detail_index;
12107:
12108: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
12109: engine_debug(l_routine||' l_break_uom_code = ' || l_break_uom_code);
12540: END LOOP;
12541: END IF; -- l_h_list_line_id_tbl.COUNT > 0
12542:
12543: --after checking each break line, if any one of the break lines satified, insert all
12544: --the break lines to qp_npreq_ldets_tmp table and qp_npreq_rltd_lines_tmp table
12545: --and do not delete the parent line
12546: -- This flag tells that either it was a range break or atleast one of the break lines was satisfied in case of point break
12547: IF (l_VALID_PBH_FLAG = G_YES) THEN
12548: --FOR J IN 1..N-1 LOOP
12551: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
12552: engine_debug('Inserting line_detail');
12553: END IF;
12554:
12555: -- julin [3805113]: bulk insert into qp_npreq_ldets_tmp
12556: IF (l_l_p_line_index_tbl.COUNT > 0) THEN
12557: FORALL i in l_l_p_line_index_tbl.FIRST..l_l_p_line_index_tbl.LAST
12558: INSERT INTO qp_npreq_ldets_tmp
12559: (LINE_DETAIL_INDEX,
12554:
12555: -- julin [3805113]: bulk insert into qp_npreq_ldets_tmp
12556: IF (l_l_p_line_index_tbl.COUNT > 0) THEN
12557: FORALL i in l_l_p_line_index_tbl.FIRST..l_l_p_line_index_tbl.LAST
12558: INSERT INTO qp_npreq_ldets_tmp
12559: (LINE_DETAIL_INDEX,
12560: LINE_DETAIL_TYPE_CODE,
12561: PRICE_BREAK_TYPE_CODE,
12562: LINE_INDEX,
12635: --set the parents PBH type. PBH can be either discount break or price list break
12636: --also set the processed_flag to indicate that this parent have been processed.
12637:
12638: /*
12639: INDX,QP_PREQ_GRP.handle_break.upd3,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
12640: */
12641: UPDATE qp_npreq_ldets_tmp --upd3
12642: SET processed_flag = G_PROCESSED -- indicates that this PBH is processed
12643: WHERE line_detail_index = p_line_detail_index;
12637:
12638: /*
12639: INDX,QP_PREQ_GRP.handle_break.upd3,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
12640: */
12641: UPDATE qp_npreq_ldets_tmp --upd3
12642: SET processed_flag = G_PROCESSED -- indicates that this PBH is processed
12643: WHERE line_detail_index = p_line_detail_index;
12644:
12645: IF (G_DEBUG_ENGINE = FND_API.G_TRUE) THEN
12808: p_pricing_phase_id NUMBER,
12809: x_status_code OUT NOCOPY VARCHAR2,
12810: x_status_text OUT NOCOPY VARCHAR2) AS
12811: /*
12812: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
12813: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
12814: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
12815: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
12816: */
12809: x_status_code OUT NOCOPY VARCHAR2,
12810: x_status_text OUT NOCOPY VARCHAR2) AS
12811: /*
12812: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
12813: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
12814: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
12815: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
12816: */
12817: CURSOR l_check_deleted_pbh_cur(p_line_index IN PLS_INTEGER) IS
12810: x_status_text OUT NOCOPY VARCHAR2) AS
12811: /*
12812: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
12813: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
12814: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
12815: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
12816: */
12817: CURSOR l_check_deleted_pbh_cur(p_line_index IN PLS_INTEGER) IS
12818: SELECT line_detail_index
12811: /*
12812: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
12813: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
12814: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
12815: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
12816: */
12817: CURSOR l_check_deleted_pbh_cur(p_line_index IN PLS_INTEGER) IS
12818: SELECT line_detail_index
12819: FROM qp_npreq_ldets_tmp
12815: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
12816: */
12817: CURSOR l_check_deleted_pbh_cur(p_line_index IN PLS_INTEGER) IS
12818: SELECT line_detail_index
12819: FROM qp_npreq_ldets_tmp
12820: WHERE created_from_list_line_type = G_PRICE_BREAK_TYPE
12821: AND line_index = p_line_index
12822: AND pricing_phase_id = p_pricing_phase_id
12823: AND pricing_status_code <> G_STATUS_NEW
12829: engine_debug('in Delete_Invalid_PBH_Children');
12830: END IF;
12831: FOR I IN l_check_deleted_pbh_cur(p_line_index) LOOP
12832: /*
12833: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.delete_invalid_pbh_children_upd1,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
12834: */
12835: /*
12836: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.delete_invalid_pbh_children_sel1,-No Index Used-,NA,NA
12837: */
12834: */
12835: /*
12836: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.delete_invalid_pbh_children_sel1,-No Index Used-,NA,NA
12837: */
12838: UPDATE qp_npreq_ldets_tmp a
12839: SET PRICING_STATUS_CODE = G_DELETED_PARENT_FAILS
12840: WHERE line_detail_index IN
12841: (SELECT related_line_detail_index
12842: FROM qp_npreq_rltd_lines_tmp
12871: AS
12872: --Index Certificate
12873: /*
12874: Can use N2 instead of N4
12875: INDX,QP_PREQ_GRP.get_eligible_price_break.l_price_break_parent_cur,qp_npreq_ldets_tmp_N2,PRICING_PHASE_ID,1
12876: INDX,QP_PREQ_GRP.get_eligible_price_break.l_price_break_parent_cur,qp_npreq_ldets_tmp_N2,PRICING_STATUS_CODE,2
12877: INDX,QP_PREQ_GRP.get_eligible_price_break.l_price_break_parent_cur,qp_npreq_ldets_tmp_N2,LINE_INDEX,3
12878: INDX,QP_PREQ_GRP.get_eligible_price_break.l_price_break_parent_cur,qp_npreq_ldets_tmp_N2,CREATED_FROM_LIST_LINE_TYPE,4
12879: */
12872: --Index Certificate
12873: /*
12874: Can use N2 instead of N4
12875: INDX,QP_PREQ_GRP.get_eligible_price_break.l_price_break_parent_cur,qp_npreq_ldets_tmp_N2,PRICING_PHASE_ID,1
12876: INDX,QP_PREQ_GRP.get_eligible_price_break.l_price_break_parent_cur,qp_npreq_ldets_tmp_N2,PRICING_STATUS_CODE,2
12877: INDX,QP_PREQ_GRP.get_eligible_price_break.l_price_break_parent_cur,qp_npreq_ldets_tmp_N2,LINE_INDEX,3
12878: INDX,QP_PREQ_GRP.get_eligible_price_break.l_price_break_parent_cur,qp_npreq_ldets_tmp_N2,CREATED_FROM_LIST_LINE_TYPE,4
12879: */
12880: -- Do price break processing only for unprocessed lines(line_index) in a phase bug 2386934
12873: /*
12874: Can use N2 instead of N4
12875: INDX,QP_PREQ_GRP.get_eligible_price_break.l_price_break_parent_cur,qp_npreq_ldets_tmp_N2,PRICING_PHASE_ID,1
12876: INDX,QP_PREQ_GRP.get_eligible_price_break.l_price_break_parent_cur,qp_npreq_ldets_tmp_N2,PRICING_STATUS_CODE,2
12877: INDX,QP_PREQ_GRP.get_eligible_price_break.l_price_break_parent_cur,qp_npreq_ldets_tmp_N2,LINE_INDEX,3
12878: INDX,QP_PREQ_GRP.get_eligible_price_break.l_price_break_parent_cur,qp_npreq_ldets_tmp_N2,CREATED_FROM_LIST_LINE_TYPE,4
12879: */
12880: -- Do price break processing only for unprocessed lines(line_index) in a phase bug 2386934
12881: -- without the not exists clause price break processing was hapenning 2 times if there is an
12874: Can use N2 instead of N4
12875: INDX,QP_PREQ_GRP.get_eligible_price_break.l_price_break_parent_cur,qp_npreq_ldets_tmp_N2,PRICING_PHASE_ID,1
12876: INDX,QP_PREQ_GRP.get_eligible_price_break.l_price_break_parent_cur,qp_npreq_ldets_tmp_N2,PRICING_STATUS_CODE,2
12877: INDX,QP_PREQ_GRP.get_eligible_price_break.l_price_break_parent_cur,qp_npreq_ldets_tmp_N2,LINE_INDEX,3
12878: INDX,QP_PREQ_GRP.get_eligible_price_break.l_price_break_parent_cur,qp_npreq_ldets_tmp_N2,CREATED_FROM_LIST_LINE_TYPE,4
12879: */
12880: -- Do price break processing only for unprocessed lines(line_index) in a phase bug 2386934
12881: -- without the not exists clause price break processing was hapenning 2 times if there is an
12882: -- order line where in price list is not passed and there is an order line where price list was passed and
12886: CURSOR l_price_break_parent_cur IS
12887: SELECT created_from_list_header_id list_header_id , created_from_list_line_id list_line_id,
12888: group_quantity ,group_amount, line_detail_index, line_index, line_quantity -- 2388011, pbh_grp_lines
12889: , created_from_list_type_code
12890: FROM qp_npreq_ldets_tmp a
12891: WHERE created_from_list_line_type = G_PRICE_BREAK_TYPE
12892: AND pricing_status_code = G_STATUS_NEW
12893: AND pricing_phase_id = p_pricing_phase_id
12894: AND nvl(processed_flag,G_NOT_PROCESSED) = G_NOT_PROCESSED;
12984: CLOSE l_price_break_parent_cur;
12985:
12986: IF (g_failed_ld_tbl_g.count > 0) THEN
12987: FORALL i in g_failed_ld_tbl_g.first .. g_failed_ld_tbl_g.last
12988: UPDATE qp_npreq_ldets_tmp
12989: SET PRICING_STATUS_CODE = G_DELETED_PBH
12990: WHERE LINE_DETAIL_INDEX = g_failed_ld_tbl_g(i);
12991: END IF;
12992:
12992:
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
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
13002: AND b.pricing_status_code = G_STATUS_NEW
13003: AND b.pricing_phase_id = G_PRICE_LIST_PHASE_ID)
13004: AND line_type_code <> G_ORDER_LEVEL;
13039: INDX,QP_PREQ_GRP.Perform_Grouping.header_level_qual_attrs_cur,qp_npreq_line_attrs_tmp_N5,PRICING_PHASE_ID,1
13040: INDX,QP_PREQ_GRP.Perform_Grouping.header_level_qual_attrs_cur,qp_npreq_line_attrs_tmp_N5,ATTRIBUTE_TYPE,2
13041: INDX,QP_PREQ_GRP.Perform_Grouping.header_level_qual_attrs_cur,qp_npreq_line_attrs_tmp_N5,PRICING_STATUS_CODE,3
13042:
13043: INDX,QP_PREQ_GRP.Perform_Grouping.header_level_qual_attrs_cur,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
13044:
13045: */
13046:
13047: --Pricing Phase Change
13052: a.LINE_INDEX,
13053: a.GROUPING_NUMBER,
13054: a.LINE_DETAIL_INDEX,
13055: COUNT(distinct CONTEXT || ATTRIBUTE || SETUP_VALUE_FROM ) NO_OF_QUALIFIERS_IN_GRP --#bug1761272
13056: FROM qp_npreq_line_attrs_tmp a,qp_npreq_ldets_tmp b
13057: WHERE a.PRICING_PHASE_ID = p_pricing_phase_id
13058: AND a.ATTRIBUTE_TYPE = G_QUALIFIER_TYPE
13059: AND a.QUALIFIER_TYPE = G_HEADER_QUALIFIER
13060: AND a.LINE_DETAIL_INDEX = b.LINE_DETAIL_INDEX
13069: INDX,QP_PREQ_GRP.Perform_Grouping.line_level_qual_attrs_cur2,qp_npreq_line_attrs_tmp_N5,PRICING_PHASE_ID,1
13070: INDX,QP_PREQ_GRP.Perform_Grouping.line_level_qual_attrs_cur2,qp_npreq_line_attrs_tmp_N5,ATTRIBUTE_TYPE,2
13071: INDX,QP_PREQ_GRP.Perform_Grouping.line_level_qual_attrs_cur2,qp_npreq_line_attrs_tmp_N5,PRICING_STATUS_CODE,3
13072:
13073: INDX,QP_PREQ_GRP.Perform_Grouping.line_level_qual_attrs_cur2,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
13074:
13075: */
13076:
13077: CURSOR line_level_qual_attrs_cur2 IS
13081: a.LINE_INDEX,
13082: a.GROUPING_NUMBER,
13083: a.LINE_DETAIL_INDEX,
13084: COUNT(distinct CONTEXT || ATTRIBUTE || SETUP_VALUE_FROM ) NO_OF_QUALIFIERS_IN_GRP --#bug1761272
13085: FROM qp_npreq_line_attrs_tmp a,qp_npreq_ldets_tmp b
13086: WHERE a.PRICING_PHASE_ID = p_pricing_phase_id
13087: AND a.ATTRIBUTE_TYPE = G_QUALIFIER_TYPE
13088: AND a.QUALIFIER_TYPE = G_LINE_QUALIFIER
13089: AND a.PRICING_STATUS_CODE IN (G_STATUS_NEW,G_DELETED_EXCLUDER,G_DELETED_BETWEEN)
13288: INDX,QP_PREQ_GRP.Perform_Grouping.product_level_attrs_cur,qp_npreq_line_attrs_tmp_N5,PRICING_PHASE_ID,1
13289: INDX,QP_PREQ_GRP.Perform_Grouping.product_level_attrs_cur,qp_npreq_line_attrs_tmp_N5,ATTRIBUTE_TYPE,2
13290: INDX,QP_PREQ_GRP.Perform_Grouping.product_level_attrs_cur,qp_npreq_line_attrs_tmp_N5,PRICING_STATUS_CODE,3
13291:
13292: INDX,QP_PREQ_GRP.Perform_Grouping.product_level_attrs_cur,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
13293:
13294: */
13295: --Pricing Phase Change
13296: CURSOR product_level_attrs_cur IS
13298: a.LIST_HEADER_ID , a.LIST_LINE_ID , a.LINE_INDEX,
13299: SUM(distinct(decode(a.attribute_type,G_PRODUCT_TYPE,1,0))) +
13300: COUNT(distinct(decode(a.attribute_type,G_PRICING_TYPE, a.context || ' ' || a.attribute || ' ' || setup_value_from ,NULL)))
13301: NO_OF_PATTS_IN_GRP -- Bug No: 7129919
13302: FROM qp_npreq_line_attrs_tmp a , qp_npreq_ldets_tmp b
13303: WHERE a.PRICING_PHASE_ID = p_pricing_phase_id
13304: AND a.CONTEXT <> G_PRIC_VOLUME_CONTEXT
13305: AND a.ATTRIBUTE_TYPE in ( G_PRICING_TYPE,G_PRODUCT_TYPE)
13306: AND a.PRICING_STATUS_CODE = G_STATUS_NEW
13880:
13881: IF l_del_tbl.FIRST IS NOT NULL THEN
13882: FORALL i in l_del_tbl.FIRST..l_del_tbl.LAST
13883: /*
13884: INDX,QP_PREQ_GRP.perform_grouping.upd1,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
13885: */
13886: UPDATE qp_npreq_ldets_tmp --upd1
13887: SET PRICING_STATUS_CODE = G_DELETED_GRP,
13888: PRICING_STATUS_TEXT = 'Deleted in header+/-line grouping'
13882: FORALL i in l_del_tbl.FIRST..l_del_tbl.LAST
13883: /*
13884: INDX,QP_PREQ_GRP.perform_grouping.upd1,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
13885: */
13886: UPDATE qp_npreq_ldets_tmp --upd1
13887: SET PRICING_STATUS_CODE = G_DELETED_GRP,
13888: PRICING_STATUS_TEXT = 'Deleted in header+/-line grouping'
13889: WHERE line_detail_index = l_del_tbl(i);
13890:
13899:
13900: IF l_success_tbl.FIRST IS NOT NULL THEN
13901: FORALL i in l_success_tbl.FIRST..l_success_tbl.LAST
13902: /*
13903: INDX,QP_PREQ_GRP.perform_grouping.upd2,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
13904: */
13905: UPDATE qp_npreq_ldets_tmp --upd2
13906: SET PRICING_STATUS_CODE = G_STATUS_NEW
13907: WHERE line_detail_index = l_success_tbl(i);
13901: FORALL i in l_success_tbl.FIRST..l_success_tbl.LAST
13902: /*
13903: INDX,QP_PREQ_GRP.perform_grouping.upd2,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
13904: */
13905: UPDATE qp_npreq_ldets_tmp --upd2
13906: SET PRICING_STATUS_CODE = G_STATUS_NEW
13907: WHERE line_detail_index = l_success_tbl(i);
13908:
13909: /* FORALL i in l_success_tbl.FIRST..l_success_tbl.LAST
14096:
14097: IF l_del_tbl.FIRST IS NOT NULL THEN
14098: FORALL i in l_del_tbl.FIRST..l_del_tbl.LAST
14099: /*
14100: INDX,QP_PREQ_GRP.perform_grouping.upd3,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
14101: */
14102: UPDATE qp_npreq_ldets_tmp --upd3
14103: SET PRICING_STATUS_CODE = G_DELETED_GRP,
14104: PRICING_STATUS_TEXT = 'Deleted in pure line grouping'
14098: FORALL i in l_del_tbl.FIRST..l_del_tbl.LAST
14099: /*
14100: INDX,QP_PREQ_GRP.perform_grouping.upd3,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
14101: */
14102: UPDATE qp_npreq_ldets_tmp --upd3
14103: SET PRICING_STATUS_CODE = G_DELETED_GRP,
14104: PRICING_STATUS_TEXT = 'Deleted in pure line grouping'
14105: WHERE line_detail_index = l_del_tbl(i);
14106:
14125:
14126: IF l_success_tbl.FIRST IS NOT NULL THEN
14127: FORALL i in l_success_tbl.FIRST..l_success_tbl.LAST
14128: /*
14129: INDX,QP_PREQ_GRP.perform_grouping.upd4,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
14130: */
14131: UPDATE qp_npreq_ldets_tmp --upd4
14132: SET PRICING_STATUS_CODE = G_STATUS_NEW
14133: WHERE line_detail_index = l_success_tbl(i);
14127: FORALL i in l_success_tbl.FIRST..l_success_tbl.LAST
14128: /*
14129: INDX,QP_PREQ_GRP.perform_grouping.upd4,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
14130: */
14131: UPDATE qp_npreq_ldets_tmp --upd4
14132: SET PRICING_STATUS_CODE = G_STATUS_NEW
14133: WHERE line_detail_index = l_success_tbl(i);
14134:
14135: /* FORALL i in l_success_tbl.FIRST..l_success_tbl.LAST
14187: engine_debug('Getting Deleted in Product Grouping');
14188:
14189: END IF;
14190: /*
14191: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
14192: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
14193: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
14194: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
14195: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
14188:
14189: END IF;
14190: /*
14191: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
14192: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
14193: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
14194: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
14195: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
14196: */
14189: END IF;
14190: /*
14191: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
14192: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
14193: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
14194: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
14195: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
14196: */
14197: UPDATE qp_npreq_ldets_tmp --upd5
14190: /*
14191: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
14192: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
14193: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
14194: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
14195: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
14196: */
14197: UPDATE qp_npreq_ldets_tmp --upd5
14198: SET PRICING_STATUS_CODE = G_DELETED_GRP
14191: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
14192: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
14193: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
14194: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
14195: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
14196: */
14197: UPDATE qp_npreq_ldets_tmp --upd5
14198: SET PRICING_STATUS_CODE = G_DELETED_GRP
14199: WHERE PRICING_PHASE_ID = p_pricing_phase_id
14193: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
14194: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
14195: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
14196: */
14197: UPDATE qp_npreq_ldets_tmp --upd5
14198: SET PRICING_STATUS_CODE = G_DELETED_GRP
14199: WHERE PRICING_PHASE_ID = p_pricing_phase_id
14200: AND PRICING_STATUS_CODE = G_STATUS_NEW
14201: AND CREATED_FROM_LIST_HEADER_ID = G_LIST_HEADER_ID_TBL_G(I)
14201: AND CREATED_FROM_LIST_HEADER_ID = G_LIST_HEADER_ID_TBL_G(I)
14202: AND CREATED_FROM_LIST_LINE_ID = G_LIST_LINE_ID_TBL_G(I)
14203: AND LINE_INDEX = G_LINE_INDEX_TBL_G(I);
14204:
14205: /* UPDATE qp_npreq_ldets_tmp
14206: SET PRICING_STATUS_CODE = G_DELETED_GRP
14207: WHERE CREATED_FROM_LIST_LINE_ID = G_LIST_LINE_ID_TBL_G(I)
14208: AND LINE_INDEX = G_LINE_INDEX_TBL_G(I); */
14209:
14229: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
14230: engine_debug('Getting Deleted in Product Grouping');
14231: END IF;
14232: /*
14233: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
14234: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
14235: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
14236: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
14237: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
14230: engine_debug('Getting Deleted in Product Grouping');
14231: END IF;
14232: /*
14233: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
14234: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
14235: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
14236: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
14237: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
14238: */
14231: END IF;
14232: /*
14233: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
14234: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
14235: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
14236: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
14237: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
14238: */
14239: UPDATE qp_npreq_ldets_tmp --upd6
14232: /*
14233: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
14234: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
14235: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
14236: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
14237: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
14238: */
14239: UPDATE qp_npreq_ldets_tmp --upd6
14240: SET PRICING_STATUS_CODE = G_DELETED_GRP
14233: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
14234: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
14235: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
14236: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
14237: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
14238: */
14239: UPDATE qp_npreq_ldets_tmp --upd6
14240: SET PRICING_STATUS_CODE = G_DELETED_GRP
14241: WHERE PRICING_PHASE_ID = p_pricing_phase_id
14235: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
14236: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
14237: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
14238: */
14239: UPDATE qp_npreq_ldets_tmp --upd6
14240: SET PRICING_STATUS_CODE = G_DELETED_GRP
14241: WHERE PRICING_PHASE_ID = p_pricing_phase_id
14242: AND PRICING_STATUS_CODE = G_STATUS_NEW
14243: AND CREATED_FROM_LIST_HEADER_ID = G_LIST_HEADER_ID_TBL_G(I)
14243: AND CREATED_FROM_LIST_HEADER_ID = G_LIST_HEADER_ID_TBL_G(I)
14244: AND CREATED_FROM_LIST_LINE_ID = G_LIST_LINE_ID_TBL_G(I)
14245: AND LINE_INDEX = G_LINE_INDEX_TBL_G(I);
14246:
14247: /* UPDATE qp_npreq_ldets_tmp
14248: SET PRICING_STATUS_CODE = G_DELETED_GRP
14249: WHERE CREATED_FROM_LIST_LINE_ID = G_LIST_LINE_ID_TBL_G(I)
14250: AND LINE_INDEX = G_LINE_INDEX_TBL_G(I); */
14251:
14269: WHERE PRICING_STATUS_CODE = G_STATUS_NEW
14270: AND LINE_INDEX = nvl(p_line_index,LINE_INDEX);
14271:
14272: --Update all the line details records with pricing_status_code = 'T' to 'N'
14273: UPDATE qp_npreq_ldets_tmp a
14274: SET PRICING_STATUS_CODE = G_STATUS_NEW
14275: WHERE PRICING_PHASE_ID = p_pricing_phase_id
14276: AND PRICING_STATUS_CODE = G_STATUS_NEW
14277: AND LINE_INDEX = nvl(p_line_index,LINE_INDEX); */
15907: OR (QPLAT_Q.DERIVED_QUALIFIER_FLAG = 'Y' AND QPSQ.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
15908: ORDER BY 1,2;
15909:
15910: /*
15911: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
15912: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
15913: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
15914: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
15915: */
15908: ORDER BY 1,2;
15909:
15910: /*
15911: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
15912: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
15913: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
15914: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
15915: */
15916: CURSOR l_chk_deleted_pbh_cur(p_line_index IN PLS_INTEGER,
15909:
15910: /*
15911: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
15912: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
15913: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
15914: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
15915: */
15916: CURSOR l_chk_deleted_pbh_cur(p_line_index IN PLS_INTEGER,
15917: p_pricing_phase_id NUMBER) IS
15910: /*
15911: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
15912: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
15913: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
15914: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
15915: */
15916: CURSOR l_chk_deleted_pbh_cur(p_line_index IN PLS_INTEGER,
15917: p_pricing_phase_id NUMBER) IS
15918: SELECT 'X'
15915: */
15916: CURSOR l_chk_deleted_pbh_cur(p_line_index IN PLS_INTEGER,
15917: p_pricing_phase_id NUMBER) IS
15918: SELECT 'X'
15919: FROM qp_npreq_ldets_tmp
15920: WHERE created_from_list_line_type = G_PRICE_BREAK_TYPE
15921: AND line_index = p_line_index
15922: AND pricing_phase_id = p_pricing_phase_id
15923: AND pricing_status_code <> G_STATUS_NEW;
16536: END LOOP; -- K
16537:
16538: end if; -- vivek, multi currency installed
16539:
16540: -- julin [3805113]: bulk insert into qp_npreq_ldets_tmp
16541: IF (D>1) THEN
16542: FORALL i in G_line_index_tbl.FIRST..G_line_index_tbl.LAST
16543: INSERT INTO qp_npreq_ldets_tmp
16544: (LINE_DETAIL_INDEX,
16539:
16540: -- julin [3805113]: bulk insert into qp_npreq_ldets_tmp
16541: IF (D>1) THEN
16542: FORALL i in G_line_index_tbl.FIRST..G_line_index_tbl.LAST
16543: INSERT INTO qp_npreq_ldets_tmp
16544: (LINE_DETAIL_INDEX,
16545: LINE_DETAIL_TYPE_CODE,
16546: LINE_INDEX,
16547: CREATED_FROM_LIST_HEADER_ID,
19173: AND LINE_TYPE_CODE <> G_ORDER_LEVEL
19174: AND (instr(PROCESS_STATUS,'FREEGOOD')=0); -- bug 2693700
19175:
19176: /*
19177: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
19178: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
19179: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
19180: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
19181: */
19174: AND (instr(PROCESS_STATUS,'FREEGOOD')=0); -- bug 2693700
19175:
19176: /*
19177: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
19178: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
19179: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
19180: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
19181: */
19182: CURSOR l_chk_deleted_pbh_cur(p_line_index IN PLS_INTEGER,
19175:
19176: /*
19177: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
19178: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
19179: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
19180: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
19181: */
19182: CURSOR l_chk_deleted_pbh_cur(p_line_index IN PLS_INTEGER,
19183: p_pricing_phase_id NUMBER) IS
19176: /*
19177: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
19178: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
19179: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
19180: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
19181: */
19182: CURSOR l_chk_deleted_pbh_cur(p_line_index IN PLS_INTEGER,
19183: p_pricing_phase_id NUMBER) IS
19184: SELECT 'X'
19181: */
19182: CURSOR l_chk_deleted_pbh_cur(p_line_index IN PLS_INTEGER,
19183: p_pricing_phase_id NUMBER) IS
19184: SELECT 'X'
19185: FROM qp_npreq_ldets_tmp
19186: WHERE created_from_list_line_type = G_PRICE_BREAK_TYPE
19187: AND line_index = p_line_index
19188: AND pricing_phase_id = p_pricing_phase_id
19189: AND pricing_status_code <> G_STATUS_NEW;
25367:
25368: INDX,QP_PREQ_GRP.select_modifiers.l_excl_cur,QP_PRICING_ATTRIBUTES_N2,LIST_LINE_ID,1
25369: INDX,QP_PREQ_GRP.select_modifiers.l_excl_cur,QP_PRICING_ATTRIBUTES_N2,EXCLUDER_FLAG,2
25370:
25371: INDX,QP_PREQ_GRP.select_modifiers.l_excl_cur,qp_npreq_ldets_tmp_N2,PRICING_PHASE_ID,1
25372: INDX,QP_PREQ_GRP.select_modifiers.l_excl_cur,qp_npreq_ldets_tmp_N2,PRICING_STATUS_CODE,2
25373:
25374: */
25375:
25368: INDX,QP_PREQ_GRP.select_modifiers.l_excl_cur,QP_PRICING_ATTRIBUTES_N2,LIST_LINE_ID,1
25369: INDX,QP_PREQ_GRP.select_modifiers.l_excl_cur,QP_PRICING_ATTRIBUTES_N2,EXCLUDER_FLAG,2
25370:
25371: INDX,QP_PREQ_GRP.select_modifiers.l_excl_cur,qp_npreq_ldets_tmp_N2,PRICING_PHASE_ID,1
25372: INDX,QP_PREQ_GRP.select_modifiers.l_excl_cur,qp_npreq_ldets_tmp_N2,PRICING_STATUS_CODE,2
25373:
25374: */
25375:
25376: -- Delete Excluded Line Details Directly if the inner select returns an excluded row
25373:
25374: */
25375:
25376: -- Delete Excluded Line Details Directly if the inner select returns an excluded row
25377: UPDATE qp_npreq_ldets_tmp a
25378: SET pricing_status_code = 'D_EXCLUDER'
25379: WHERE pricing_phase_id = p_pricing_phase_id
25380: AND pricing_status_code = G_STATUS_NEW
25381: AND EXISTS ( SELECT/*+ ORDERED USE_NL(c) index(c qp_preq_line_attrs_tmp_N2) index(b QP_PRICING_ATTRIBUTES_N2) l_excl_cur */
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
29465: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_check_modlist_cur,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
29466: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_check_modlist_cur,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
29467:
29468: INDX,lQP_PREQ_GRP.create_qualifier_from_list._check_modlist_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
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
29465: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_check_modlist_cur,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
29466: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_check_modlist_cur,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
29467:
29468: INDX,lQP_PREQ_GRP.create_qualifier_from_list._check_modlist_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
29469: INDX,lQP_PREQ_GRP.create_qualifier_from_list._check_modlist_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
29462:
29463: /*
29464: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_check_modlist_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
29465: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_check_modlist_cur,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
29466: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_check_modlist_cur,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
29467:
29468: INDX,lQP_PREQ_GRP.create_qualifier_from_list._check_modlist_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
29469: INDX,lQP_PREQ_GRP.create_qualifier_from_list._check_modlist_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
29470: INDX,lQP_PREQ_GRP.create_qualifier_from_list._check_modlist_cur,qp_npreq_line_attrs_tmp_N2,CONTEXT,3
29474: */
29475: --check if passed in price list indeed has qualified
29476: CURSOR l_check_modlist_cur(p_line_index IN PLS_INTEGER) IS
29477: SELECT /*+ ORDERED index(qplat qp_preq_line_attrs_tmp_n2) l_check_modlist_cur */ 'X'
29478: FROM qp_npreq_ldets_tmp qpld,
29479: qp_npreq_line_attrs_tmp qplat
29480: WHERE qpld.line_index = p_line_index
29481: AND qpld.pricing_phase_id = G_PRICE_LIST_PHASE_ID
29482: AND qpld.pricing_status_code = G_STATUS_NEW
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,
29498: qp_npreq_line_attrs_tmp qplat
29499: WHERE qpld.line_index = qpll.line_index
29500: AND qpld.pricing_phase_id = G_PRICE_LIST_PHASE_ID
29501: AND qpld.pricing_status_code = G_STATUS_NEW
29524: AND line_index = p_line_index
29525: AND ROWNUM = 1; --just one record need to be returned
29526:
29527: /*
29528: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
29529: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
29530: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
29531: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
29532: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,PRICING_STATUS_CODE,5
29525: AND ROWNUM = 1; --just one record need to be returned
29526:
29527: /*
29528: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
29529: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
29530: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
29531: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
29532: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,PRICING_STATUS_CODE,5
29533: */
29526:
29527: /*
29528: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
29529: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
29530: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
29531: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
29532: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,PRICING_STATUS_CODE,5
29533: */
29534: CURSOR l_get_pl_cur(p_line_index IN PLS_INTEGER) IS
29527: /*
29528: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
29529: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
29530: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
29531: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
29532: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,PRICING_STATUS_CODE,5
29533: */
29534: CURSOR l_get_pl_cur(p_line_index IN PLS_INTEGER) IS
29535: SELECT created_from_list_header_id
29528: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
29529: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
29530: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
29531: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
29532: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,PRICING_STATUS_CODE,5
29533: */
29534: CURSOR l_get_pl_cur(p_line_index IN PLS_INTEGER) IS
29535: SELECT created_from_list_header_id
29536: FROM qp_npreq_ldets_tmp
29532: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,PRICING_STATUS_CODE,5
29533: */
29534: CURSOR l_get_pl_cur(p_line_index IN PLS_INTEGER) IS
29535: SELECT created_from_list_header_id
29536: FROM qp_npreq_ldets_tmp
29537: WHERE line_index = p_line_index
29538: and created_from_list_line_type in (G_PRICE_LIST_TYPE,G_BY_PBH) -- Bug#2882115
29539: and pricing_status_code = G_STATUS_NEW
29540: and pricing_phase_id = G_PRICE_LIST_PHASE_ID;
29754: x_status_code OUT NOCOPY VARCHAR2,
29755: x_status_text OUT NOCOPY VARCHAR2) AS
29756: /*
29757: -- Can use N1 instead of N3
29758: INDX,QP_PREQ_GRP.get_reason.l_reason_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
29759: */
29760: CURSOR l_reason_cur(p_line_index IN PLS_INTEGER) IS
29761: SELECT distinct pricing_status_code
29762: FROM qp_npreq_ldets_tmp
29758: INDX,QP_PREQ_GRP.get_reason.l_reason_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
29759: */
29760: CURSOR l_reason_cur(p_line_index IN PLS_INTEGER) IS
29761: SELECT distinct pricing_status_code
29762: FROM qp_npreq_ldets_tmp
29763: WHERE line_index = p_line_index
29764: AND pricing_status_code <> G_STATUS_NEW;
29765:
29766: J PLS_INTEGER:=0;
29837: ORDER BY PROCESSING_ORDER;
29838:
29839: --for the case where p_control_rec.price_flat = 'P' (G_PHASE)
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: */
29838:
29839: --for the case where p_control_rec.price_flat = 'P' (G_PHASE)
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,
29868: QPLT.GROUP_QUANTITY,
29869: QPLT.GROUP_AMOUNT,
29870: QPLT.LINE_QUANTITY, --2388011_new
29871: QPLT.SELECTED_VOLUME_ATTR -- 2388011_new
29872: FROM qp_npreq_ldets_tmp QPLT
29873: WHERE QPLT.LINE_INDEX = L_LINE_INDEX
29874: AND QPLT.PRICING_STATUS_CODE = G_STATUS_NEW
29875: UNION
29876: -- hints added as temporary R12 fix for 5573442
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
29905: AND QPLT.CREATED_FROM_LIST_TYPE_CODE <> G_CHARGES_HEADER
30077:
30078: --For manual discount, we want pricing_group sequence to be NULL
30079: /*
30080: -- This update is not needed as it is taken care of by the setup
30081: INDX,QP_PREQ_GRP.call_calculation_engine.call_calculation_engine_upd1,qp_npreq_ldets_tmp_N6,AUTOMATIC_FLAG,1
30082: */
30083: /* UPDATE qp_npreq_ldets_tmp
30084: SET PRICING_GROUP_SEQUENCE = NULL
30085: WHERE AUTOMATIC_FLAG = G_NO; */
30079: /*
30080: -- This update is not needed as it is taken care of by the setup
30081: INDX,QP_PREQ_GRP.call_calculation_engine.call_calculation_engine_upd1,qp_npreq_ldets_tmp_N6,AUTOMATIC_FLAG,1
30082: */
30083: /* UPDATE qp_npreq_ldets_tmp
30084: SET PRICING_GROUP_SEQUENCE = NULL
30085: WHERE AUTOMATIC_FLAG = G_NO; */
30086:
30087:
30823: engine_debug ('In Line Detail Update Information1');
30824: END IF;
30825: IF (G_LINE_DETAIL_INDEX_TBL_D.COUNT > 0) THEN
30826: FORALL O IN G_LINE_DETAIL_INDEX_TBL_D.FIRST .. G_LINE_DETAIL_INDEX_TBL_D.LAST
30827: UPDATE qp_npreq_ldets_tmp
30828: SET ADJUSTMENT_AMOUNT = G_ADJUSTMENT_AMOUNT_TBL_D(O),
30829: --LINE_QUANTITY = G_QUALIFIER_VALUE_TBL_D(O),-- 2388011, ASK RAVI, either priced_quantity,group_quantity,group_amount
30830: BENEFIT_QTY = G_BENEFIT_QUANTITY_TBL_D(O),
30831: OPERAND_VALUE = G_OPERAND_VALUE_TBL_D(O) --2388011_new, remove comma
30839: END IF;
30840: -- Update All the Line Details Information at 1 Shot for all Lines
30841: IF (G_LINE_DETAIL_INDEX_TBL_D.COUNT > 0) THEN
30842: FORALL O IN G_LINE_DETAIL_INDEX_TBL_D.FIRST .. G_LINE_DETAIL_INDEX_TBL_D.LAST
30843: UPDATE /*+ index(QP_NPREQ_LDETS_TMP QP_PREQ_LDETS_TMP_U1) */ qp_npreq_ldets_tmp --5658579
30844: SET ADJUSTMENT_AMOUNT = G_ADJUSTMENT_AMOUNT_TBL_D(O),
30845: --LINE_QUANTITY = G_QUALIFIER_VALUE_TBL_D(O), -- 2388011, ASK RAVI, either priced_quantity,group_quantity,group_amount
30846: BENEFIT_QTY = G_BENEFIT_QUANTITY_TBL_D(O),
30847: OPERAND_VALUE = G_OPERAND_VALUE_TBL_D(O),
30857: END IF;
30858: -- Update All the Related Line Details Information at 1 Shot for all Lines
30859: IF (G_LINE_DETAIL_INDEX_TBL_R.COUNT > 0) THEN
30860: FORALL O IN G_LINE_DETAIL_INDEX_TBL_R.FIRST .. G_LINE_DETAIL_INDEX_TBL_R.LAST
30861: UPDATE qp_npreq_ldets_tmp
30862: SET ADJUSTMENT_AMOUNT = G_ADJUSTMENT_AMOUNT_TBL_R(O),
30863: LINE_QUANTITY = G_LINE_QUANTITY_TBL_R(O)
30864: WHERE LINE_DETAIL_INDEX = G_LINE_DETAIL_INDEX_TBL_R(O);
30865: END IF;
30929: x_status_code OUT NOCOPY VARCHAR2,
30930: x_status_text OUT NOCOPY VARCHAR2 ) AS
30931:
30932: /*
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
30930: x_status_text OUT NOCOPY VARCHAR2 ) AS
30931:
30932: /*
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: */
30931:
30932: /*
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
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;
31472: IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
31473: -- OR l_formula_based_price IS NOT NULL) THEN
31474: IF (i.OPERAND_CALCULATION_CODE in ('UNIT_PRICE','LIST_PRICE')) THEN
31475: /*
31476: INDX,QP_PREQ_GRP.formula_processing.upd1,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
31477: INDX,QP_PREQ_GRP.formula_processing.upd1,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
31478: INDX,QP_PREQ_GRP.formula_processing.upd1,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
31479: */
31480: UPDATE qp_npreq_ldets_tmp --upd1
31473: -- OR l_formula_based_price IS NOT NULL) THEN
31474: IF (i.OPERAND_CALCULATION_CODE in ('UNIT_PRICE','LIST_PRICE')) THEN
31475: /*
31476: INDX,QP_PREQ_GRP.formula_processing.upd1,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
31477: INDX,QP_PREQ_GRP.formula_processing.upd1,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
31478: INDX,QP_PREQ_GRP.formula_processing.upd1,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
31479: */
31480: UPDATE qp_npreq_ldets_tmp --upd1
31481: SET OPERAND_VALUE = l_formula_based_price
31474: IF (i.OPERAND_CALCULATION_CODE in ('UNIT_PRICE','LIST_PRICE')) THEN
31475: /*
31476: INDX,QP_PREQ_GRP.formula_processing.upd1,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
31477: INDX,QP_PREQ_GRP.formula_processing.upd1,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
31478: INDX,QP_PREQ_GRP.formula_processing.upd1,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
31479: */
31480: UPDATE qp_npreq_ldets_tmp --upd1
31481: SET OPERAND_VALUE = l_formula_based_price
31482: WHERE CREATED_FROM_LIST_LINE_ID = i.CREATED_FROM_LIST_LINE_ID
31476: INDX,QP_PREQ_GRP.formula_processing.upd1,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
31477: INDX,QP_PREQ_GRP.formula_processing.upd1,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
31478: INDX,QP_PREQ_GRP.formula_processing.upd1,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
31479: */
31480: UPDATE qp_npreq_ldets_tmp --upd1
31481: SET OPERAND_VALUE = l_formula_based_price
31482: WHERE CREATED_FROM_LIST_LINE_ID = i.CREATED_FROM_LIST_LINE_ID
31483: AND PRICING_PHASE_ID = p_pricing_phase_id
31484: AND LINE_INDEX = i.line_index
31493: PRICING_STATUS_TEXT ='Priced by formula'
31494: WHERE LINE_INDEX = i.line_index;
31495: ELSIF (i.OPERAND_CALCULATION_CODE = 'PERCENT_PRICE') THEN
31496: /*
31497: INDX,QP_PREQ_GRP.formula_processing.upd3,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
31498: INDX,QP_PREQ_GRP.formula_processing.upd3,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
31499: INDX,QP_PREQ_GRP.formula_processing.upd3,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
31500: */
31501: UPDATE qp_npreq_ldets_tmp --upd3
31494: WHERE LINE_INDEX = i.line_index;
31495: ELSIF (i.OPERAND_CALCULATION_CODE = 'PERCENT_PRICE') THEN
31496: /*
31497: INDX,QP_PREQ_GRP.formula_processing.upd3,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
31498: INDX,QP_PREQ_GRP.formula_processing.upd3,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
31499: INDX,QP_PREQ_GRP.formula_processing.upd3,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
31500: */
31501: UPDATE qp_npreq_ldets_tmp --upd3
31502: SET OPERAND_VALUE = l_formula_based_price
31495: ELSIF (i.OPERAND_CALCULATION_CODE = 'PERCENT_PRICE') THEN
31496: /*
31497: INDX,QP_PREQ_GRP.formula_processing.upd3,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
31498: INDX,QP_PREQ_GRP.formula_processing.upd3,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
31499: INDX,QP_PREQ_GRP.formula_processing.upd3,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
31500: */
31501: UPDATE qp_npreq_ldets_tmp --upd3
31502: SET OPERAND_VALUE = l_formula_based_price
31503: WHERE CREATED_FROM_LIST_LINE_ID = i.CREATED_FROM_LIST_LINE_ID
31497: INDX,QP_PREQ_GRP.formula_processing.upd3,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
31498: INDX,QP_PREQ_GRP.formula_processing.upd3,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
31499: INDX,QP_PREQ_GRP.formula_processing.upd3,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
31500: */
31501: UPDATE qp_npreq_ldets_tmp --upd3
31502: SET OPERAND_VALUE = l_formula_based_price
31503: WHERE CREATED_FROM_LIST_LINE_ID = i.CREATED_FROM_LIST_LINE_ID
31504: AND PRICING_PHASE_ID = p_pricing_phase_id
31505: AND LINE_INDEX = i.line_index
31514: PRICING_STATUS_TEXT ='Priced by formula'
31515: WHERE LINE_INDEX = i.line_index;
31516: ELSE -- BLOCK_PRICE, % , AMT , NEWPRICE
31517: /*
31518: INDX,QP_PREQ_GRP.formula_processing.upd5,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
31519: INDX,QP_PREQ_GRP.formula_processing.upd5,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
31520: INDX,QP_PREQ_GRP.formula_processing.upd5,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
31521: */
31522: UPDATE qp_npreq_ldets_tmp --upd5
31515: WHERE LINE_INDEX = i.line_index;
31516: ELSE -- BLOCK_PRICE, % , AMT , NEWPRICE
31517: /*
31518: INDX,QP_PREQ_GRP.formula_processing.upd5,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
31519: INDX,QP_PREQ_GRP.formula_processing.upd5,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
31520: INDX,QP_PREQ_GRP.formula_processing.upd5,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
31521: */
31522: UPDATE qp_npreq_ldets_tmp --upd5
31523: SET OPERAND_VALUE = l_formula_based_price
31516: ELSE -- BLOCK_PRICE, % , AMT , NEWPRICE
31517: /*
31518: INDX,QP_PREQ_GRP.formula_processing.upd5,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
31519: INDX,QP_PREQ_GRP.formula_processing.upd5,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
31520: INDX,QP_PREQ_GRP.formula_processing.upd5,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
31521: */
31522: UPDATE qp_npreq_ldets_tmp --upd5
31523: SET OPERAND_VALUE = l_formula_based_price
31524: WHERE CREATED_FROM_LIST_LINE_ID = i.CREATED_FROM_LIST_LINE_ID
31518: INDX,QP_PREQ_GRP.formula_processing.upd5,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
31519: INDX,QP_PREQ_GRP.formula_processing.upd5,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
31520: INDX,QP_PREQ_GRP.formula_processing.upd5,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
31521: */
31522: UPDATE qp_npreq_ldets_tmp --upd5
31523: SET OPERAND_VALUE = l_formula_based_price
31524: WHERE CREATED_FROM_LIST_LINE_ID = i.CREATED_FROM_LIST_LINE_ID
31525: AND PRICING_PHASE_ID = p_pricing_phase_id
31526: AND LINE_INDEX = i.line_index
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;
31620: --Delete From qp_nformula_step_values_tmp;
31621: --Delete From QP_PREQ_LINE_ATTRS_FORMULA_TMP;
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);
31632: END IF;
31633: select count(*) into l_count from qp_npreq_ldets_tmp;
31634: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
31635: engine_debug('qp_npreq_ldets_tmp '||l_count);
31636: END IF;
31637: END IF;
31631: engine_debug('qp_npreq_line_attrs_tmp '||l_count);
31632: END IF;
31633: select count(*) into l_count from qp_npreq_ldets_tmp;
31634: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
31635: engine_debug('qp_npreq_ldets_tmp '||l_count);
31636: END IF;
31637: END IF;
31638: --added by yangli for Java Engine project
31639: ELSE
31705: );
31706:
31707: /*
31708: -- Can use N2 instead of N4
31709: INDX,QP_PREQ_GRP.set_order_level_rltd.l_line_detail_index_cur,qp_npreq_ldets_tmp_N2,PRICING_PHASE_ID,1
31710: INDX,QP_PREQ_GRP.set_order_level_rltd.l_line_detail_index_cur,qp_npreq_ldets_tmp_N2,PRICING_STATUS_CODE,2
31711: */
31712: CURSOR l_line_detail_index_cur is
31713: SELECT line_detail_index
31706:
31707: /*
31708: -- Can use N2 instead of N4
31709: INDX,QP_PREQ_GRP.set_order_level_rltd.l_line_detail_index_cur,qp_npreq_ldets_tmp_N2,PRICING_PHASE_ID,1
31710: INDX,QP_PREQ_GRP.set_order_level_rltd.l_line_detail_index_cur,qp_npreq_ldets_tmp_N2,PRICING_STATUS_CODE,2
31711: */
31712: CURSOR l_line_detail_index_cur is
31713: SELECT line_detail_index
31714: FROM qp_npreq_ldets_tmp
31710: INDX,QP_PREQ_GRP.set_order_level_rltd.l_line_detail_index_cur,qp_npreq_ldets_tmp_N2,PRICING_STATUS_CODE,2
31711: */
31712: CURSOR l_line_detail_index_cur is
31713: SELECT line_detail_index
31714: FROM qp_npreq_ldets_tmp
31715: WHERE PRICING_STATUS_CODE = G_STATUS_NEW
31716: AND PRICING_PHASE_ID = p_pricing_phase_id
31717: AND MODIFIER_LEVEL_CODE = G_ORDER_LEVEL;
31718:
32184: ORDER BY QPP.PHASE_SEQUENCE,SEARCH_FLAG DESC; */
32185:
32186: /*
32187: Can use N2 instead of N4
32188: INDX,QP_PREQ_GRP.price_request.l_get_line_index_cur,qp_npreq_ldets_tmp_N2,PRICING_PHASE_ID,1
32189: INDX,QP_PREQ_GRP.price_request.l_get_line_index_cur,qp_npreq_ldets_tmp_N2,PRICING_STATUS_CODE,2
32190: */
32191: CURSOR l_get_line_index_cur(p_pricing_phase_id NUMBER) IS
32192: SELECT DISTINCT LINE_INDEX
32185:
32186: /*
32187: Can use N2 instead of N4
32188: INDX,QP_PREQ_GRP.price_request.l_get_line_index_cur,qp_npreq_ldets_tmp_N2,PRICING_PHASE_ID,1
32189: INDX,QP_PREQ_GRP.price_request.l_get_line_index_cur,qp_npreq_ldets_tmp_N2,PRICING_STATUS_CODE,2
32190: */
32191: CURSOR l_get_line_index_cur(p_pricing_phase_id NUMBER) IS
32192: SELECT DISTINCT LINE_INDEX
32193: FROM qp_npreq_ldets_tmp
32189: INDX,QP_PREQ_GRP.price_request.l_get_line_index_cur,qp_npreq_ldets_tmp_N2,PRICING_STATUS_CODE,2
32190: */
32191: CURSOR l_get_line_index_cur(p_pricing_phase_id NUMBER) IS
32192: SELECT DISTINCT LINE_INDEX
32193: FROM qp_npreq_ldets_tmp
32194: WHERE PRICING_PHASE_ID = p_pricing_phase_id
32195: AND PRICING_STATUS_CODE = G_STATUS_NEW;
32196:
32197: /*
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
32235: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
32236: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
32237: */
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
32235: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
32236: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
32237: */
32238: CURSOR l_chk_deleted_pbh_cur(p_line_index IN PLS_INTEGER,
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
32235: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
32236: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
32237: */
32238: CURSOR l_chk_deleted_pbh_cur(p_line_index IN PLS_INTEGER,
32239: p_pricing_phase_id NUMBER) IS
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
32235: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
32236: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
32237: */
32238: CURSOR l_chk_deleted_pbh_cur(p_line_index IN PLS_INTEGER,
32239: p_pricing_phase_id NUMBER) IS
32240: SELECT 'X'
32237: */
32238: CURSOR l_chk_deleted_pbh_cur(p_line_index IN PLS_INTEGER,
32239: p_pricing_phase_id NUMBER) IS
32240: SELECT 'X'
32241: FROM qp_npreq_ldets_tmp
32242: WHERE created_from_list_line_type = G_PRICE_BREAK_TYPE
32243: AND line_index = p_line_index
32244: AND pricing_phase_id = p_pricing_phase_id
32245: AND pricing_status_code <> G_STATUS_NEW;
32327: AND trunc(sysdate) BETWEEN trunc(nvl(a.START_DATE_ACTIVE,sysdate))
32328: AND trunc(nvl(a.END_DATE_ACTIVE,sysdate));
32329:
32330: /*
32331: INDX,QP_PREQ_GRP.price_request.l_get_max_line_detail_index,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
32332: */
32333: CURSOR l_get_max_line_detail_index IS
32334: SELECT MAX(LINE_DETAIL_INDEX)
32335: FROM qp_npreq_ldets_tmp;
32331: INDX,QP_PREQ_GRP.price_request.l_get_max_line_detail_index,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
32332: */
32333: CURSOR l_get_max_line_detail_index IS
32334: SELECT MAX(LINE_DETAIL_INDEX)
32335: FROM qp_npreq_ldets_tmp;
32336:
32337: /*
32338: INDX,QP_PREQ_GRP.price_request.l_limit_check_cur,qp_npreq_ldets_tmp_N4,HEADER_LIMIT_EXISTS,1
32339: INDX,QP_PREQ_GRP.price_request.l_limit_check_cur,qp_npreq_ldets_tmp_N4,PRICING_STATUS_CODE,2
32334: SELECT MAX(LINE_DETAIL_INDEX)
32335: FROM qp_npreq_ldets_tmp;
32336:
32337: /*
32338: INDX,QP_PREQ_GRP.price_request.l_limit_check_cur,qp_npreq_ldets_tmp_N4,HEADER_LIMIT_EXISTS,1
32339: INDX,QP_PREQ_GRP.price_request.l_limit_check_cur,qp_npreq_ldets_tmp_N4,PRICING_STATUS_CODE,2
32340: */
32341: CURSOR l_limit_check_cur IS
32342: SELECT HEADER_LIMIT_EXISTS,LINE_LIMIT_EXISTS
32335: FROM qp_npreq_ldets_tmp;
32336:
32337: /*
32338: INDX,QP_PREQ_GRP.price_request.l_limit_check_cur,qp_npreq_ldets_tmp_N4,HEADER_LIMIT_EXISTS,1
32339: INDX,QP_PREQ_GRP.price_request.l_limit_check_cur,qp_npreq_ldets_tmp_N4,PRICING_STATUS_CODE,2
32340: */
32341: CURSOR l_limit_check_cur IS
32342: SELECT HEADER_LIMIT_EXISTS,LINE_LIMIT_EXISTS
32343: FROM qp_npreq_ldets_tmp
32339: INDX,QP_PREQ_GRP.price_request.l_limit_check_cur,qp_npreq_ldets_tmp_N4,PRICING_STATUS_CODE,2
32340: */
32341: CURSOR l_limit_check_cur IS
32342: SELECT HEADER_LIMIT_EXISTS,LINE_LIMIT_EXISTS
32343: FROM qp_npreq_ldets_tmp
32344: WHERE PRICING_STATUS_CODE = G_STATUS_NEW
32345: AND HEADER_LIMIT_EXISTS = G_YES ;
32346:
32347: /*
32929:
32930: --Put back the changes for bug 2457983
32931: --in OC case where caller passes adjustments, the adjustments are inserted
32932: --but G_LINE_DETAIL_INDEX is not incremented which causes a
32933: --unique constraint violation on qp_npreq_ldets_tmp_U1
32934: --hence need to fetch the maximum line_detail_index
32935: G_LINE_DETAIL_INDEX := nvl(l_max_line_detail_index,1);
32936: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
32937: engine_debug('G_LINE_DETAIL_INDEX '||G_LINE_DETAIL_INDEX);