[Home] [Help]
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
3486: /*
3487: INDX,QP_PREQ_GRP.set_status_code.set_status_code_upd2,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
3488: */
3489: -- shulin fix bug1745788, widen substr to 2000
3490: --bug 14464004
3491: UPDATE /*+ index(qp_npreq_ldets_tmp qp_preq_ldets_tmp_n1) */ qp_npreq_ldets_tmp --upd2
3487: INDX,QP_PREQ_GRP.set_status_code.set_status_code_upd2,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
3488: */
3489: -- shulin fix bug1745788, widen substr to 2000
3490: --bug 14464004
3491: UPDATE /*+ index(qp_npreq_ldets_tmp qp_preq_ldets_tmp_n1) */ qp_npreq_ldets_tmp --upd2
3492: SET PRICING_STATUS_CODE = p_status_code,
3493: PRICING_STATUS_TEXT =
3494: substr(pricing_status_code||'+'||p_status_code||'+'||p_status_text,1,2000)
3495: WHERE LINE_INDEX = p_line_index
3564: END SET_PROCESSED_CODE;
3565:
3566: /*+----------------------------------------------------
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
5500: x_status_text OUT NOCOPY VARCHAR2)
5501: AS
5502: l_routine VARCHAR2(240):= 'Routine:QP_PREQ_GRP.INSERT_LDETS';
5503: BEGIN
5504: INSERT INTO qp_npreq_ldets_tmp
5505: (LINE_DETAIL_INDEX,
5506: LINE_DETAIL_TYPE_CODE,
5507: PRICE_BREAK_TYPE_CODE,
5508: LINE_INDEX,
5674:
5675: BEGIN
5676:
5677: FORALL i in p_line_index.FIRST..p_line_index.LAST
5678: INSERT INTO qp_npreq_ldets_tmp
5679: (LINE_DETAIL_INDEX,
5680: LINE_DETAIL_TYPE_CODE,
5681: PRICE_BREAK_TYPE_CODE,
5682: LINE_INDEX,
5792:
5793: BEGIN
5794:
5795: FORALL i in p_line_index.FIRST..p_line_index.LAST
5796: INSERT INTO qp_npreq_ldets_tmp
5797: (LINE_DETAIL_INDEX,
5798: LINE_DETAIL_TYPE_CODE,
5799: PRICE_BREAK_TYPE_CODE,
5800: LINE_INDEX,
6197: END IF;
6198: --added by yangli for Java Engine project
6199:
6200: FORALL i in p_line_index.FIRST..p_line_index.LAST
6201: INSERT INTO qp_npreq_ldets_tmp
6202: (LINE_DETAIL_INDEX,
6203: LINE_DETAIL_TYPE_CODE,
6204: PRICE_BREAK_TYPE_CODE,
6205: LINE_INDEX,
6410:
6411: BEGIN
6412:
6413: FORALL i in p_line_index.FIRST..p_line_index.LAST
6414: INSERT INTO qp_npreq_ldets_tmp
6415: (LINE_DETAIL_INDEX,
6416: LINE_DETAIL_TYPE_CODE,
6417: PRICE_BREAK_TYPE_CODE,
6418: LINE_INDEX,
7066: END IF;
7067:
7068: --added by yangli for Java Engine project
7069: FORALL i in p_line_index.FIRST..p_line_index.LAST
7070: INSERT INTO qp_npreq_ldets_tmp
7071: (LINE_DETAIL_INDEX,
7072: LINE_DETAIL_TYPE_CODE,
7073: PRICE_BREAK_TYPE_CODE,
7074: LINE_INDEX,
8045: l_routine VARCHAR2(40) := 'Delete_Duplicate_List_Lines';
8046:
8047: CURSOR get_duplicate_list_lines_count IS
8048: SELECT count(*)
8049: FROM qp_npreq_ldets_tmp
8050: WHERE pricing_status_code = G_STATUS_NEW
8051: AND pricing_phase_id = p_pricing_phase_id
8052: GROUP BY line_index,created_from_list_line_id
8053: HAVING count(*) > 1;
8068: engine_debug('Duplicate List Lines Count : ' || l_count);
8069: END IF;
8070:
8071: IF (l_count > 0) THEN
8072: UPDATE qp_npreq_ldets_tmp a
8073: SET pricing_status_code = 'DUPLICATE_LIST_LINE'
8074: WHERE pricing_status_code = G_STATUS_NEW
8075: AND pricing_phase_id = p_pricing_phase_id
8076: AND rowid not in (SELECT min(rowid)
8073: SET pricing_status_code = 'DUPLICATE_LIST_LINE'
8074: WHERE pricing_status_code = G_STATUS_NEW
8075: AND pricing_phase_id = p_pricing_phase_id
8076: AND rowid not in (SELECT min(rowid)
8077: FROM qp_npreq_ldets_tmp
8078: WHERE pricing_status_code = G_STATUS_NEW
8079: AND pricing_phase_id = p_pricing_phase_id
8080: GROUP BY line_index,created_from_list_line_id);
8081: END IF;
8193: -- Delete lines which are actually ask_for in setup , but are not asked for in the Order
8194: -- Because Engine selects these lines irrespective of their ask_for_flag
8195: -- Index Certificate
8196: /*
8197: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
8198: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
8199: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
8200: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
8201: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
8194: -- Because Engine selects these lines irrespective of their ask_for_flag
8195: -- Index Certificate
8196: /*
8197: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
8198: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
8199: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
8200: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
8201: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
8202: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_line_attrs_tmp_N2,CONTEXT,3
8195: -- Index Certificate
8196: /*
8197: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
8198: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
8199: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
8200: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
8201: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
8202: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_line_attrs_tmp_N2,CONTEXT,3
8203: INDX,QP_Resolve_Incompatability_PVTRUN.best_price_for_phase.upd1,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE,4
8210: l_pricing_status_code := QP_PREQ_GRP.G_STATUS_INCOMP_LOGIC;
8211: END IF;
8212:
8213:
8214: UPDATE qp_npreq_ldets_tmp a --upd1
8215: SET PRICING_STATUS_CODE = l_pricing_status_code
8216: WHERE a.ASK_FOR_FLAG = QP_PREQ_GRP.G_YES
8217: AND a.PRICING_PHASE_ID = p_pricing_phase_id
8218: AND a.LINE_INDEX = p_line_index
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:
9468: INDX,QP_PREQ_GRP.populate_output.l_qual_cur,qp_npreq_line_attrs_tmp_N3,LINE_DETAIL_INDEX,1
9469: INDX,QP_PREQ_GRP.populate_output.l_qual_cur,qp_npreq_line_attrs_tmp_N3,ATTRIBUTE_TYPE,2
9470: */
9478: QPLAT.COMPARISON_OPERATOR_TYPE_CODE,
9479: QPLAT.VALIDATED_FLAG,
9480: QPLAT.PRICING_STATUS_CODE,
9481: QPLAT.PRICING_STATUS_TEXT
9482: FROM qp_npreq_ldets_tmp QPLD ,
9483: qp_npreq_line_attrs_tmp QPLAT
9484: WHERE QPLD.LINE_DETAIL_INDEX = QPLAT.LINE_DETAIL_INDEX
9485: AND QPLD.PRICING_STATUS_CODE = G_STATUS_NEW
9486: AND QPLAT.ATTRIBUTE_TYPE = 'QUALIFIER';
9486: AND QPLAT.ATTRIBUTE_TYPE = 'QUALIFIER';
9487: --AND QPLAT.PRICING_STATUS_CODE = G_STATUS_NEW;
9488:
9489: /*
9490: INDX,QP_PREQ_GRP.populate_output.l_pricing_attr_cur,qp_npreq_ldets_tmp_N4,PRICING_STATUS_CODE,1
9491:
9492: INDX,QP_PREQ_GRP.populate_output.l_pricing_attr_cur,qp_npreq_line_attrs_tmp_N3,LINE_DETAIL_INDEX,1
9493: INDX,QP_PREQ_GRP.populate_output.l_pricing_attr_cur,qp_npreq_line_attrs_tmp_N3,ATTRIBUTE_TYPE,2
9494: */
9501: QPLAT_PRICING.COMPARISON_OPERATOR_TYPE_CODE,
9502: QPLAT_PRICING.LINE_DETAIL_INDEX,
9503: QPLAT_PRICING.LINE_INDEX,
9504: QPLAT_PRICING.VALIDATED_FLAG
9505: FROM qp_npreq_ldets_tmp QPLD ,
9506: qp_npreq_line_attrs_tmp QPLAT_PRICING
9507: WHERE QPLD.LINE_DETAIL_INDEX = QPLAT_PRICING.LINE_DETAIL_INDEX
9508: AND QPLD.PRICING_STATUS_CODE = G_STATUS_NEW
9509: AND QPLAT_PRICING.ATTRIBUTE_TYPE IN ('PRICING','PRODUCT');
9511:
9512: /*
9513: INDX,QP_PREQ_GRP.populate_output.l_ldets_cur,QP_LIST_LINES_PK,LIST_LINE_ID,1
9514:
9515: INDX,QP_PREQ_GRP.populate_output.l_ldets_cur,qp_npreq_ldets_tmp_N4,PRICING_STATUS_CODE,1
9516:
9517: INDX,QP_PREQ_GRP.populate_output.l_ldets_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
9518: */
9519: CURSOR l_ldets_cur IS
9581: b.COMMENTS,
9582: b.break_uom_code, /* proration */
9583: b.break_uom_context, /* proration */
9584: b.break_uom_attribute /* proration */
9585: FROM qp_npreq_ldets_tmp a,
9586: QP_LIST_LINES b
9587: WHERE a.CREATED_FROM_LIST_LINE_ID = b.LIST_LINE_ID
9588: AND a.PRICING_STATUS_CODE = G_STATUS_NEW;
9589:
10369: INDX,QP_PREQ_GRP.eliminate_multiple_hierarchies.l_get_multiple_exclude_cur,qp_npreq_line_attrs_tmp_N5,PRICING_STATUS_CODE,3
10370: INDX,QP_PREQ_GRP.eliminate_multiple_hierarchies.l_get_multiple_exclude_cur,qp_npreq_line_attrs_tmp_N5,LINE_INDEX,4
10371: INDX,QP_PREQ_GRP.eliminate_multiple_hierarchies.l_get_multiple_exclude_cur,qp_npreq_line_attrs_tmp_N5,COMPARISON_OPERATOR_CODE,5
10372:
10373: INDX,QP_PREQ_GRP.eliminate_multiple_hierarchies.l_get_multiple_exclude_cur,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
10374:
10375: */
10376:
10377: --Pricing Phase Change
10378: CURSOR l_get_multiple_exclude_cur IS
10379: SELECT /*+ ORDERED USE_NL(b) index(a qp_preq_line_attrs_tmp_N5) l_get_multiple_exclude_cur */
10380: distinct a.line_index , a.list_header_id , a.list_line_id , a.grouping_number grouping_no,
10381: a.context , a.attribute , a.setup_value_from
10382: FROM qp_npreq_line_attrs_tmp a , qp_npreq_ldets_tmp b
10383: WHERE
10384: a.comparison_operator_type_code = 'NOT ='
10385: AND a.attribute_type IN (G_PRICING_TYPE,G_QUALIFIER_TYPE)
10386: AND a.pricing_status_code = G_STATUS_NEW
10505: AND pricing_status_code = G_STATUS_NEW -- Expect an issue because of this join condition
10506: AND attribute_type IN (G_PRICING_TYPE,G_QUALIFIER_TYPE);
10507:
10508: /* FORALL j IN l_del_list_line_tbl.FIRST .. l_del_list_line_tbl.LAST
10509: UPDATE qp_npreq_ldets_tmp
10510: SET pricing_status_code = G_DELETED_EXCLUDER
10511: WHERE created_from_list_header_id = l_del_list_header_tbl(j)
10512: AND created_from_list_line_id = l_del_list_line_tbl(j)
10513: AND line_index = l_del_line_index_tbl(j)
10564: select /*+ ORDERED USE_NL(c a ) index(a qp_pricing_attributes_n2) line_grp_cur */
10565: b.created_from_list_line_id list_line_id , c.context , c.attribute ,
10566: sum(qp_number.canonical_to_number(c.value_from)) group_value
10567: from
10568: qp_npreq_ldets_tmp b , qp_npreq_line_attrs_tmp c , qp_pricing_attributes a
10569: where
10570: b.modifier_level_code = G_LINE_GROUP
10571: and b.pricing_phase_id = p_pricing_phase_id
10572: and c.pricing_status_code = G_STATUS_UNCHANGED
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:
10658:
10659: /*
10660: INDX,QP_PREQ_GRP.process_line_group.upd2,-No Index Used-,NA,NA
10661: */
10662: UPDATE qp_npreq_ldets_tmp --upd2
10663: SET LINE_QUANTITY = l_group_quantity, -- 2388011_latest, shu fix pbh_grp_amt
10664: GROUP_QUANTITY = l_group_quantity, -- 2388011_latest, shu fix pbh_grp_amt
10665: SELECTED_VOLUME_ATTR = G_QUANTITY -- 2388011_latest
10666: WHERE CREATED_FROM_LIST_LINE_ID = j.list_line_id;
10709: INDX,QP_PREQ_GRP11.process_line_group.upd4,-No Index Used-,NA,NA
10710: */
10711: IF (G_LINE_INDEX_TBL_G.COUNT > 0) THEN
10712: FORALL I IN G_LINE_INDEX_TBL_G.FIRST .. G_LINE_INDEX_TBL_G.LAST
10713: UPDATE qp_npreq_ldets_tmp --upd4
10714: SET GROUP_AMOUNT = G_GROUP_AMOUNT_PER_UNIT_TBL_G(I),
10715: LINE_QUANTITY = l_group_amount, -- 2388011_latest
10716: SELECTED_VOLUME_ATTR = G_AMOUNT -- 2388011_latest
10717: WHERE CREATED_FROM_LIST_LINE_ID = j.list_line_id
10755: BEGIN
10756:
10757: IF (p_line_detail_index IS NOT NULL) THEN
10758: /*
10759: INDX,QP_PREQ_GRP.is_line_group.is_line_group_sel1,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
10760: */
10761: SELECT 'X'
10762: INTO l_dummy
10763: FROM qp_npreq_ldets_tmp
10759: INDX,QP_PREQ_GRP.is_line_group.is_line_group_sel1,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
10760: */
10761: SELECT 'X'
10762: INTO l_dummy
10763: FROM qp_npreq_ldets_tmp
10764: WHERE line_detail_index = p_line_detail_index
10765: AND modifier_level_code = G_LINE_GROUP;
10766: ELSE
10767: /*
10764: WHERE line_detail_index = p_line_detail_index
10765: AND modifier_level_code = G_LINE_GROUP;
10766: ELSE
10767: /*
10768: INDX,QP_PREQ_GRP.is_line_group.is_line_group_sel2,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
10769: */
10770: SELECT 'X'
10771: INTO l_dummy
10772: FROM qp_npreq_ldets_tmp
10768: INDX,QP_PREQ_GRP.is_line_group.is_line_group_sel2,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
10769: */
10770: SELECT 'X'
10771: INTO l_dummy
10772: FROM qp_npreq_ldets_tmp
10773: WHERE line_index = p_line_index
10774: AND modifier_level_code = G_LINE_GROUP;
10775: END IF;
10776:
10904: /*
10905: INDX,QP_PREQ_GRP.update_request_line_status.update_request_line_status_upd1,-No Index Used-,NA,NA
10906: */
10907: /*
10908: INDX,QP_PREQ_GRP.update_request_line_status.sel1,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
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:
10905: INDX,QP_PREQ_GRP.update_request_line_status.update_request_line_status_upd1,-No Index Used-,NA,NA
10906: */
10907: /*
10908: INDX,QP_PREQ_GRP.update_request_line_status.sel1,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
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
10906: */
10907: /*
10908: INDX,QP_PREQ_GRP.update_request_line_status.sel1,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
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
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
10918: WHERE a.line_index = b.line_index
10919: AND b.pricing_status_code = G_STATUS_NEW
10920: AND b.pricing_phase_id = G_PRICE_LIST_PHASE_ID)
10921: AND line_type_code <> G_ORDER_LEVEL;
10919: AND b.pricing_status_code = G_STATUS_NEW
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
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
10929: AND b.pricing_status_code = G_STATUS_NEW
10930: AND b.pricing_phase_id = G_PRICE_LIST_PHASE_ID)
10931: AND line_type_code <> G_ORDER_LEVEL
11003:
11004: ELSE
11005:
11006: /*
11007: INDX,QP_PREQ_GRP.update_request_line_status.sel3,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
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:
11004: ELSE
11005:
11006: /*
11007: INDX,QP_PREQ_GRP.update_request_line_status.sel3,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
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
11005:
11006: /*
11007: INDX,QP_PREQ_GRP.update_request_line_status.sel3,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
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
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
11017: AND b.pricing_status_code = G_STATUS_NEW
11018: AND b.pricing_phase_id = G_PRICE_LIST_PHASE_ID)
11019: AND line_type_code <> G_ORDER_LEVEL
11052: INDX,QP_PREQ_GRP.Evaluate_Between.l_pric_between_cur,qp_npreq_line_attrs_tmp_N5,LINE_INDEX,4
11053: INDX,QP_PREQ_GRP.Evaluate_Between.l_pric_between_cur,qp_npreq_line_attrs_tmp_N5,COMPARISON_OPERATOR_CODE,5
11054: INDX,QP_PREQ_GRP.Evaluate_Between.l_pric_between_cur,qp_npreq_line_attrs_tmp_N5,DATATYPE,6
11055:
11056: INDX,QP_PREQ_GRP.Evaluate_Between.l_pric_between_cur,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
11057:
11058: */
11059:
11060: CURSOR l_pric_between_cur(p_datatype VARCHAR2) IS
11070: pricing.product_uom_code,
11071: pricing.attribute,
11072: pricing.context,
11073: pricing.attribute_type
11074: FROM qp_npreq_line_attrs_tmp pricing,qp_npreq_ldets_tmp ldets
11075: WHERE pricing.comparison_operator_type_code = G_OPERATOR_BETWEEN
11076: AND pricing.datatype = p_datatype
11077: AND pricing.pricing_phase_id = G_PRICE_LIST_PHASE_ID
11078: AND pricing.attribute_type IN (G_PRICING_TYPE,G_QUALIFIER_TYPE)
11089: INDX,QP_PREQ_GRP.Evaluate_Between.l_mod_between_cur,qp_npreq_line_attrs_tmp_N5,LINE_INDEX,4
11090: INDX,QP_PREQ_GRP.Evaluate_Between.l_mod_between_cur,qp_npreq_line_attrs_tmp_N5,COMPARISON_OPERATOR_CODE,5
11091: INDX,QP_PREQ_GRP.Evaluate_Between.l_mod_between_cur,qp_npreq_line_attrs_tmp_N5,DATATYPE,6
11092:
11093: INDX,QP_PREQ_GRP.Evaluate_Between.l_pric_between_cur,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
11094:
11095: */
11096:
11097: CURSOR l_mod_between_cur(p_datatype VARCHAR2) IS
11106: pricing.product_uom_code,
11107: pricing.attribute,
11108: pricing.context,
11109: pricing.attribute_type
11110: FROM qp_npreq_line_attrs_tmp pricing,qp_npreq_ldets_tmp ldets
11111: WHERE pricing.comparison_operator_type_code = G_OPERATOR_BETWEEN
11112: AND pricing.datatype = p_datatype
11113: AND pricing.pricing_phase_id = p_pricing_phase_id
11114: AND pricing.attribute_type = G_PRICING_TYPE
11125: INDX,QP_PREQ_GRP.Evaluate_Between.l_pric_character_between_cur,qp_npreq_line_attrs_tmp_N5,LINE_INDEX,4
11126: INDX,QP_PREQ_GRP.Evaluate_Between.l_pric_character_between_cur,qp_npreq_line_attrs_tmp_N5,COMPARISON_OPERATOR_CODE,5
11127: INDX,QP_PREQ_GRP.Evaluate_Between.l_pric_character_between_cur,qp_npreq_line_attrs_tmp_N5,DATATYPE,6
11128:
11129: INDX,QP_PREQ_GRP.Evaluate_Between.l_pric_character_between_cur,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
11130:
11131: */
11132:
11133: CURSOR l_pric_character_between_cur IS
11140: b.product_uom_code,
11141: b.attribute,
11142: b.context,
11143: b.attribute_type
11144: FROM qp_npreq_line_attrs_tmp b,qp_npreq_ldets_tmp c
11145: WHERE comparison_operator_type_code = G_OPERATOR_BETWEEN
11146: AND b.pricing_phase_id = G_PRICE_LIST_PHASE_ID
11147: AND datatype in (G_DATE_X,G_DATE_Y,G_VARCHAR)
11148: AND attribute_type IN (G_PRICING_TYPE,G_QUALIFIER_TYPE)
11160: INDX,QP_PREQ_GRP.Evaluate_Between.l_mod_character_between_cur,qp_npreq_line_attrs_tmp_N5,LINE_INDEX,4
11161: INDX,QP_PREQ_GRP.Evaluate_Between.l_mod_character_between_cur,qp_npreq_line_attrs_tmp_N5,COMPARISON_OPERATOR_CODE,5
11162: INDX,QP_PREQ_GRP.Evaluate_Between.l_mod_character_between_cur,qp_npreq_line_attrs_tmp_N5,DATATYPE,6
11163:
11164: INDX,QP_PREQ_GRP.Evaluate_Between.l_pric_character_between_cur,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
11165:
11166: */
11167:
11168: CURSOR l_mod_character_between_cur IS
11175: b.product_uom_code,
11176: b.attribute,
11177: b.context,
11178: b.attribute_type
11179: FROM qp_npreq_line_attrs_tmp b,qp_npreq_ldets_tmp c
11180: WHERE comparison_operator_type_code = G_OPERATOR_BETWEEN
11181: AND b.pricing_phase_id = p_pricing_phase_id
11182: AND datatype in (G_DATE_X,G_DATE_Y,G_VARCHAR)
11183: AND attribute_type = G_PRICING_TYPE
11393:
11394: IF (I.group_quantity IS NOT NULL and I.group_quantity <> l_value_from_num and l_value_from_num IS NOT NULL
11395: and p_line_group_flag IS NOT NULL) THEN
11396: /*
11397: INDX,QP_PREQ_GRP.evaluate_between.evaluate_between_upd1,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
11398: */
11399: UPDATE qp_npreq_ldets_tmp
11400: SET GROUP_QUANTITY = l_value_from_num
11401: WHERE LINE_INDEX = i.line_index
11395: and p_line_group_flag IS NOT NULL) THEN
11396: /*
11397: INDX,QP_PREQ_GRP.evaluate_between.evaluate_between_upd1,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
11398: */
11399: UPDATE qp_npreq_ldets_tmp
11400: SET GROUP_QUANTITY = l_value_from_num
11401: WHERE LINE_INDEX = i.line_index
11402: AND LINE_DETAIL_INDEX = i.line_detail_index;
11403: END IF;
11413:
11414: IF (I.group_amount IS NOT NULL and l_value_from_num IS NOT NULL -- shu
11415: and p_line_group_flag IS NOT NULL) THEN
11416: /*
11417: INDX,QP_PREQ_GRP.evaluate_between.evaluate_between_upd2,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
11418: */
11419:
11420:
11421: -- begin shu, fix group of lines lumpsum based on ITEM_AMOUNT
11443: QP_PREQ_GRP.engine_debug('group_value: '||l_group_amount);
11444: END IF;
11445: -- end shu fix
11446:
11447: UPDATE qp_npreq_ldets_tmp
11448: SET GROUP_AMOUNT = l_group_amount -- shu
11449: WHERE LINE_INDEX = i.line_index
11450: AND LINE_DETAIL_INDEX = i.line_detail_index;
11451:
11459: QP_PREQ_GRP.engine_debug('Line Index: ' || i.line_index || ' LD Index : ' || i.line_detail_index ||
11460: ' '||l_value_from_num);
11461: END IF;
11462: /*
11463: INDX,QP_PREQ_GRP.evaluate_between.evaluate_between_upd3,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
11464: */
11465: UPDATE qp_npreq_ldets_tmp
11466: SET LINE_QUANTITY = l_value_from_num
11467: WHERE LINE_INDEX = i.line_index
11461: END IF;
11462: /*
11463: INDX,QP_PREQ_GRP.evaluate_between.evaluate_between_upd3,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
11464: */
11465: UPDATE qp_npreq_ldets_tmp
11466: SET LINE_QUANTITY = l_value_from_num
11467: WHERE LINE_INDEX = i.line_index
11468: AND LINE_DETAIL_INDEX = i.line_detail_index;
11469: END IF;
11559:
11560: IF (I.group_quantity IS NOT NULL and I.group_quantity <> l_value_from_num and l_value_from_num IS NOT NULL
11561: and p_line_group_flag IS NOT NULL) THEN
11562: /*
11563: INDX,QP_PREQ_GRP.evaluate_between.evaluate_between_upd1,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
11564: */
11565: UPDATE qp_npreq_ldets_tmp
11566: SET GROUP_QUANTITY = l_value_from_num, --2388011_new
11567: SELECTED_VOLUME_ATTR = l_selected_volume_attr -- 2388011_new
11561: and p_line_group_flag IS NOT NULL) THEN
11562: /*
11563: INDX,QP_PREQ_GRP.evaluate_between.evaluate_between_upd1,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
11564: */
11565: UPDATE qp_npreq_ldets_tmp
11566: SET GROUP_QUANTITY = l_value_from_num, --2388011_new
11567: SELECTED_VOLUME_ATTR = l_selected_volume_attr -- 2388011_new
11568: WHERE LINE_INDEX = i.line_index
11569: AND LINE_DETAIL_INDEX = i.line_detail_index;
11580: END IF;
11581: IF (I.group_amount IS NOT NULL and l_value_from_num IS NOT NULL
11582: and p_line_group_flag IS NOT NULL) THEN
11583: /*
11584: INDX,QP_PREQ_GRP.evaluate_between.evaluate_between_upd2,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
11585: */
11586:
11587:
11588: -- [julin/5085310]
11627: QP_PREQ_GRP.engine_debug('i.group_amount: '||i.group_amount);
11628: QP_PREQ_GRP.engine_debug('group_value: '||l_group_amount);
11629: END IF;
11630:
11631: UPDATE qp_npreq_ldets_tmp
11632: SET GROUP_AMOUNT = l_group_amount, -- 2388011_new, fix group of lines lumpsum based on ITEM_AMOUNT
11633: SELECTED_VOLUME_ATTR = l_selected_volume_attr -- 2388011_new
11634: WHERE LINE_INDEX = i.line_index
11635: AND LINE_DETAIL_INDEX = i.line_detail_index;
11643: QP_PREQ_GRP.engine_debug('Line Index: ' || i.line_index || ' LD Index : ' || i.line_detail_index ||
11644: ' '||l_value_from_num);
11645: END IF;
11646: /*
11647: INDX,QP_PREQ_GRP.evaluate_between.evaluate_between_upd3,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
11648: */
11649: UPDATE qp_npreq_ldets_tmp
11650: SET LINE_QUANTITY = l_value_from_num, --2388011_new
11651: SELECTED_VOLUME_ATTR = l_selected_volume_attr --2388011_new
11645: END IF;
11646: /*
11647: INDX,QP_PREQ_GRP.evaluate_between.evaluate_between_upd3,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
11648: */
11649: UPDATE qp_npreq_ldets_tmp
11650: SET LINE_QUANTITY = l_value_from_num, --2388011_new
11651: SELECTED_VOLUME_ATTR = l_selected_volume_attr --2388011_new
11652: WHERE LINE_INDEX = i.line_index
11653: AND LINE_DETAIL_INDEX = i.line_detail_index;
11663: -- attribute BETWEEN fails
11664: FORALL I IN 1 .. l_line_detail_tbl.COUNT
11665:
11666: /*
11667: INDX,QP_PREQ_GRP.evaluate_between.upd4,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
11668: */
11669: UPDATE qp_npreq_ldets_tmp --upd4
11670: SET PRICING_STATUS_CODE = G_DELETED_BETWEEN
11671: WHERE LINE_DETAIL_INDEX = l_line_detail_tbl(i);
11665:
11666: /*
11667: INDX,QP_PREQ_GRP.evaluate_between.upd4,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
11668: */
11669: UPDATE qp_npreq_ldets_tmp --upd4
11670: SET PRICING_STATUS_CODE = G_DELETED_BETWEEN
11671: WHERE LINE_DETAIL_INDEX = l_line_detail_tbl(i);
11672: qp_debug_util.tstop('Evaluate_Between');
11673: EXCEPTION
12235: engine_debug('UOM p_group_quantity :'||p_group_quantity);
12236:
12237: END IF;
12238: /*
12239: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
12240: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
12241: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
12242: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
12243: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
12236:
12237: END IF;
12238: /*
12239: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
12240: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
12241: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
12242: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
12243: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
12244: */
12237: END IF;
12238: /*
12239: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
12240: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
12241: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
12242: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
12243: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
12244: */
12245:
12238: /*
12239: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
12240: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
12241: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
12242: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
12243: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
12244: */
12245:
12246: UPDATE qp_npreq_ldets_tmp --upd1
12239: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
12240: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
12241: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
12242: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
12243: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
12244: */
12245:
12246: UPDATE qp_npreq_ldets_tmp --upd1
12247: SET GROUP_QUANTITY = l_quantity, --2388011_new
12242: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
12243: INDX,QP_PREQ_GRP.handle_break.upd1,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
12244: */
12245:
12246: UPDATE qp_npreq_ldets_tmp --upd1
12247: SET GROUP_QUANTITY = l_quantity, --2388011_new
12248: SELECTED_VOLUME_ATTR = l_selected_volume_attr -- 2388011_new, ASK RAVI, commented out??
12249: WHERE CREATED_FROM_LIST_HEADER_ID = p_list_header_id
12250: AND PRICING_PHASE_ID = p_pricing_phase_id
12321: -- This LINE_QUANTITY is column is needed for knowing the right value for
12322: -- PBH based on modifier level code 'LINE' for calculation purposes
12323:
12324: /*
12325: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
12326: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
12327: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
12328: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
12329: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
12322: -- PBH based on modifier level code 'LINE' for calculation purposes
12323:
12324: /*
12325: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
12326: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
12327: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
12328: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
12329: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
12330: */
12323:
12324: /*
12325: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
12326: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
12327: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
12328: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
12329: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
12330: */
12331:
12324: /*
12325: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
12326: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
12327: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
12328: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
12329: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
12330: */
12331:
12332: UPDATE qp_npreq_ldets_tmp --upd2
12325: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
12326: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
12327: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
12328: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
12329: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
12330: */
12331:
12332: UPDATE qp_npreq_ldets_tmp --upd2
12333: SET LINE_QUANTITY = l_quantity, --2388011_new
12328: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
12329: INDX,QP_PREQ_GRP.handle_break.upd2,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
12330: */
12331:
12332: UPDATE qp_npreq_ldets_tmp --upd2
12333: SET LINE_QUANTITY = l_quantity, --2388011_new
12334: SELECTED_VOLUME_ATTR = l_selected_volume_attr -- 2388011_new
12335: WHERE CREATED_FROM_LIST_HEADER_ID = p_list_header_id
12336: AND PRICING_PHASE_ID = p_pricing_phase_id
12391:
12392: if l_first_time_flag = 'Y' then
12393: select break_uom_code, break_uom_context, break_uom_attribute
12394: into l_break_uom_code, l_break_uom_context, l_break_uom_attribute
12395: from qp_npreq_ldets_tmp
12396: where line_detail_index = p_line_detail_index;
12397:
12398: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
12399: engine_debug(l_routine||' l_break_uom_code = ' || l_break_uom_code);
12830: END LOOP;
12831: END IF; -- l_h_list_line_id_tbl.COUNT > 0
12832:
12833: --after checking each break line, if any one of the break lines satified, insert all
12834: --the break lines to qp_npreq_ldets_tmp table and qp_npreq_rltd_lines_tmp table
12835: --and do not delete the parent line
12836: -- This flag tells that either it was a range break or atleast one of the break lines was satisfied in case of point break
12837: IF (l_VALID_PBH_FLAG = G_YES) THEN
12838: --FOR J IN 1..N-1 LOOP
12841: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
12842: engine_debug('Inserting line_detail');
12843: END IF;
12844:
12845: -- julin [3805113]: bulk insert into qp_npreq_ldets_tmp
12846: IF (l_l_p_line_index_tbl.COUNT > 0) THEN
12847: FORALL i in l_l_p_line_index_tbl.FIRST..l_l_p_line_index_tbl.LAST
12848: INSERT INTO qp_npreq_ldets_tmp
12849: (LINE_DETAIL_INDEX,
12844:
12845: -- julin [3805113]: bulk insert into qp_npreq_ldets_tmp
12846: IF (l_l_p_line_index_tbl.COUNT > 0) THEN
12847: FORALL i in l_l_p_line_index_tbl.FIRST..l_l_p_line_index_tbl.LAST
12848: INSERT INTO qp_npreq_ldets_tmp
12849: (LINE_DETAIL_INDEX,
12850: LINE_DETAIL_TYPE_CODE,
12851: PRICE_BREAK_TYPE_CODE,
12852: LINE_INDEX,
12929: --set the parents PBH type. PBH can be either discount break or price list break
12930: --also set the processed_flag to indicate that this parent have been processed.
12931:
12932: /*
12933: INDX,QP_PREQ_GRP.handle_break.upd3,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
12934: */
12935: UPDATE qp_npreq_ldets_tmp --upd3
12936: SET processed_flag = G_PROCESSED -- indicates that this PBH is processed
12937: WHERE line_detail_index = p_line_detail_index;
12931:
12932: /*
12933: INDX,QP_PREQ_GRP.handle_break.upd3,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
12934: */
12935: UPDATE qp_npreq_ldets_tmp --upd3
12936: SET processed_flag = G_PROCESSED -- indicates that this PBH is processed
12937: WHERE line_detail_index = p_line_detail_index;
12938:
12939: IF (G_DEBUG_ENGINE = FND_API.G_TRUE) THEN
13102: p_pricing_phase_id NUMBER,
13103: x_status_code OUT NOCOPY VARCHAR2,
13104: x_status_text OUT NOCOPY VARCHAR2) AS
13105: /*
13106: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
13107: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
13108: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
13109: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
13110: */
13103: x_status_code OUT NOCOPY VARCHAR2,
13104: x_status_text OUT NOCOPY VARCHAR2) AS
13105: /*
13106: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
13107: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
13108: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
13109: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
13110: */
13111: CURSOR l_check_deleted_pbh_cur(p_line_index IN PLS_INTEGER) IS
13104: x_status_text OUT NOCOPY VARCHAR2) AS
13105: /*
13106: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
13107: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
13108: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
13109: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
13110: */
13111: CURSOR l_check_deleted_pbh_cur(p_line_index IN PLS_INTEGER) IS
13112: SELECT line_detail_index
13105: /*
13106: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
13107: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
13108: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
13109: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
13110: */
13111: CURSOR l_check_deleted_pbh_cur(p_line_index IN PLS_INTEGER) IS
13112: SELECT line_detail_index
13113: FROM qp_npreq_ldets_tmp
13109: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
13110: */
13111: CURSOR l_check_deleted_pbh_cur(p_line_index IN PLS_INTEGER) IS
13112: SELECT line_detail_index
13113: FROM qp_npreq_ldets_tmp
13114: WHERE created_from_list_line_type = G_PRICE_BREAK_TYPE
13115: AND line_index = p_line_index
13116: AND pricing_phase_id = p_pricing_phase_id
13117: AND pricing_status_code <> G_STATUS_NEW
13123: engine_debug('in Delete_Invalid_PBH_Children');
13124: END IF;
13125: FOR I IN l_check_deleted_pbh_cur(p_line_index) LOOP
13126: /*
13127: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.delete_invalid_pbh_children_upd1,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
13128: */
13129: /*
13130: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.delete_invalid_pbh_children_sel1,-No Index Used-,NA,NA
13131: */
13128: */
13129: /*
13130: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.delete_invalid_pbh_children_sel1,-No Index Used-,NA,NA
13131: */
13132: UPDATE qp_npreq_ldets_tmp a
13133: SET PRICING_STATUS_CODE = G_DELETED_PARENT_FAILS
13134: WHERE line_detail_index IN
13135: (SELECT related_line_detail_index
13136: FROM qp_npreq_rltd_lines_tmp
13165: AS
13166: --Index Certificate
13167: /*
13168: Can use N2 instead of N4
13169: INDX,QP_PREQ_GRP.get_eligible_price_break.l_price_break_parent_cur,qp_npreq_ldets_tmp_N2,PRICING_PHASE_ID,1
13170: INDX,QP_PREQ_GRP.get_eligible_price_break.l_price_break_parent_cur,qp_npreq_ldets_tmp_N2,PRICING_STATUS_CODE,2
13171: INDX,QP_PREQ_GRP.get_eligible_price_break.l_price_break_parent_cur,qp_npreq_ldets_tmp_N2,LINE_INDEX,3
13172: INDX,QP_PREQ_GRP.get_eligible_price_break.l_price_break_parent_cur,qp_npreq_ldets_tmp_N2,CREATED_FROM_LIST_LINE_TYPE,4
13173: */
13166: --Index Certificate
13167: /*
13168: Can use N2 instead of N4
13169: INDX,QP_PREQ_GRP.get_eligible_price_break.l_price_break_parent_cur,qp_npreq_ldets_tmp_N2,PRICING_PHASE_ID,1
13170: INDX,QP_PREQ_GRP.get_eligible_price_break.l_price_break_parent_cur,qp_npreq_ldets_tmp_N2,PRICING_STATUS_CODE,2
13171: INDX,QP_PREQ_GRP.get_eligible_price_break.l_price_break_parent_cur,qp_npreq_ldets_tmp_N2,LINE_INDEX,3
13172: INDX,QP_PREQ_GRP.get_eligible_price_break.l_price_break_parent_cur,qp_npreq_ldets_tmp_N2,CREATED_FROM_LIST_LINE_TYPE,4
13173: */
13174: -- Do price break processing only for unprocessed lines(line_index) in a phase bug 2386934
13167: /*
13168: Can use N2 instead of N4
13169: INDX,QP_PREQ_GRP.get_eligible_price_break.l_price_break_parent_cur,qp_npreq_ldets_tmp_N2,PRICING_PHASE_ID,1
13170: INDX,QP_PREQ_GRP.get_eligible_price_break.l_price_break_parent_cur,qp_npreq_ldets_tmp_N2,PRICING_STATUS_CODE,2
13171: INDX,QP_PREQ_GRP.get_eligible_price_break.l_price_break_parent_cur,qp_npreq_ldets_tmp_N2,LINE_INDEX,3
13172: INDX,QP_PREQ_GRP.get_eligible_price_break.l_price_break_parent_cur,qp_npreq_ldets_tmp_N2,CREATED_FROM_LIST_LINE_TYPE,4
13173: */
13174: -- Do price break processing only for unprocessed lines(line_index) in a phase bug 2386934
13175: -- without the not exists clause price break processing was hapenning 2 times if there is an
13168: Can use N2 instead of N4
13169: INDX,QP_PREQ_GRP.get_eligible_price_break.l_price_break_parent_cur,qp_npreq_ldets_tmp_N2,PRICING_PHASE_ID,1
13170: INDX,QP_PREQ_GRP.get_eligible_price_break.l_price_break_parent_cur,qp_npreq_ldets_tmp_N2,PRICING_STATUS_CODE,2
13171: INDX,QP_PREQ_GRP.get_eligible_price_break.l_price_break_parent_cur,qp_npreq_ldets_tmp_N2,LINE_INDEX,3
13172: INDX,QP_PREQ_GRP.get_eligible_price_break.l_price_break_parent_cur,qp_npreq_ldets_tmp_N2,CREATED_FROM_LIST_LINE_TYPE,4
13173: */
13174: -- Do price break processing only for unprocessed lines(line_index) in a phase bug 2386934
13175: -- without the not exists clause price break processing was hapenning 2 times if there is an
13176: -- order line where in price list is not passed and there is an order line where price list was passed and
13177: -- price break processing was done for the passed price list line. Price break processing is hapenning again
13178: -- when big search is done for the line where price list not passed.
13179: -- process only the price break lines that are not processed -- Bug #2717117
13180: CURSOR l_price_break_parent_cur IS
13181: SELECT /*+ index(a qp_npreq_ldets_tmp_N2) */ --bug 9362867
13182: created_from_list_header_id list_header_id , created_from_list_line_id list_line_id,
13183: group_quantity ,group_amount, line_detail_index, line_index, line_quantity -- 2388011, pbh_grp_lines
13184: , created_from_list_type_code
13185: FROM qp_npreq_ldets_tmp a
13181: SELECT /*+ index(a qp_npreq_ldets_tmp_N2) */ --bug 9362867
13182: created_from_list_header_id list_header_id , created_from_list_line_id list_line_id,
13183: group_quantity ,group_amount, line_detail_index, line_index, line_quantity -- 2388011, pbh_grp_lines
13184: , created_from_list_type_code
13185: FROM qp_npreq_ldets_tmp a
13186: WHERE created_from_list_line_type = G_PRICE_BREAK_TYPE
13187: AND pricing_status_code = G_STATUS_NEW
13188: AND pricing_phase_id = p_pricing_phase_id
13189: AND nvl(processed_flag,G_NOT_PROCESSED) = G_NOT_PROCESSED;
13279: CLOSE l_price_break_parent_cur;
13280:
13281: IF (g_failed_ld_tbl_g.count > 0) THEN
13282: FORALL i in g_failed_ld_tbl_g.first .. g_failed_ld_tbl_g.last
13283: UPDATE qp_npreq_ldets_tmp
13284: SET PRICING_STATUS_CODE = G_DELETED_PBH
13285: WHERE LINE_DETAIL_INDEX = g_failed_ld_tbl_g(i);
13286: END IF;
13287:
13287:
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
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
13297: AND b.pricing_status_code = G_STATUS_NEW
13298: AND b.pricing_phase_id = G_PRICE_LIST_PHASE_ID)
13299: AND line_type_code <> G_ORDER_LEVEL;
13335: INDX,QP_PREQ_GRP.Perform_Grouping.header_level_qual_attrs_cur,qp_npreq_line_attrs_tmp_N5,PRICING_PHASE_ID,1
13336: INDX,QP_PREQ_GRP.Perform_Grouping.header_level_qual_attrs_cur,qp_npreq_line_attrs_tmp_N5,ATTRIBUTE_TYPE,2
13337: INDX,QP_PREQ_GRP.Perform_Grouping.header_level_qual_attrs_cur,qp_npreq_line_attrs_tmp_N5,PRICING_STATUS_CODE,3
13338:
13339: INDX,QP_PREQ_GRP.Perform_Grouping.header_level_qual_attrs_cur,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
13340:
13341: */
13342:
13343: --Pricing Phase Change
13348: a.LINE_INDEX,
13349: a.GROUPING_NUMBER,
13350: a.LINE_DETAIL_INDEX,
13351: COUNT(distinct CONTEXT || ATTRIBUTE || SETUP_VALUE_FROM ) NO_OF_QUALIFIERS_IN_GRP --#bug1761272
13352: FROM qp_npreq_line_attrs_tmp a,qp_npreq_ldets_tmp b
13353: WHERE a.PRICING_PHASE_ID = p_pricing_phase_id
13354: AND a.ATTRIBUTE_TYPE = G_QUALIFIER_TYPE
13355: AND a.QUALIFIER_TYPE = G_HEADER_QUALIFIER
13356: AND a.LINE_DETAIL_INDEX = b.LINE_DETAIL_INDEX
13365: INDX,QP_PREQ_GRP.Perform_Grouping.line_level_qual_attrs_cur2,qp_npreq_line_attrs_tmp_N5,PRICING_PHASE_ID,1
13366: INDX,QP_PREQ_GRP.Perform_Grouping.line_level_qual_attrs_cur2,qp_npreq_line_attrs_tmp_N5,ATTRIBUTE_TYPE,2
13367: INDX,QP_PREQ_GRP.Perform_Grouping.line_level_qual_attrs_cur2,qp_npreq_line_attrs_tmp_N5,PRICING_STATUS_CODE,3
13368:
13369: INDX,QP_PREQ_GRP.Perform_Grouping.line_level_qual_attrs_cur2,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
13370:
13371: */
13372:
13373: CURSOR line_level_qual_attrs_cur2 IS
13377: a.LINE_INDEX,
13378: a.GROUPING_NUMBER,
13379: a.LINE_DETAIL_INDEX,
13380: COUNT(distinct CONTEXT || ATTRIBUTE || SETUP_VALUE_FROM ) NO_OF_QUALIFIERS_IN_GRP --#bug1761272
13381: FROM qp_npreq_line_attrs_tmp a,qp_npreq_ldets_tmp b
13382: WHERE a.PRICING_PHASE_ID = p_pricing_phase_id
13383: AND a.ATTRIBUTE_TYPE = G_QUALIFIER_TYPE
13384: AND a.QUALIFIER_TYPE = G_LINE_QUALIFIER
13385: AND a.PRICING_STATUS_CODE IN (G_STATUS_NEW,G_DELETED_EXCLUDER,G_DELETED_BETWEEN)
13584: INDX,QP_PREQ_GRP.Perform_Grouping.product_level_attrs_cur,qp_npreq_line_attrs_tmp_N5,PRICING_PHASE_ID,1
13585: INDX,QP_PREQ_GRP.Perform_Grouping.product_level_attrs_cur,qp_npreq_line_attrs_tmp_N5,ATTRIBUTE_TYPE,2
13586: INDX,QP_PREQ_GRP.Perform_Grouping.product_level_attrs_cur,qp_npreq_line_attrs_tmp_N5,PRICING_STATUS_CODE,3
13587:
13588: INDX,QP_PREQ_GRP.Perform_Grouping.product_level_attrs_cur,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
13589:
13590: */
13591: --Pricing Phase Change
13592: CURSOR product_level_attrs_cur IS
13594: a.LIST_HEADER_ID , a.LIST_LINE_ID , a.LINE_INDEX,
13595: SUM(distinct(decode(a.attribute_type,G_PRODUCT_TYPE,1,0))) +
13596: COUNT(distinct(decode(a.attribute_type,G_PRICING_TYPE, a.context || ' ' || a.attribute || ' ' || setup_value_from ,NULL)))
13597: NO_OF_PATTS_IN_GRP -- Bug No: 7129919
13598: FROM qp_npreq_line_attrs_tmp a , qp_npreq_ldets_tmp b
13599: WHERE a.PRICING_PHASE_ID = p_pricing_phase_id
13600: AND a.CONTEXT <> G_PRIC_VOLUME_CONTEXT
13601: AND a.ATTRIBUTE_TYPE in ( G_PRICING_TYPE,G_PRODUCT_TYPE)
13602: AND a.PRICING_STATUS_CODE = G_STATUS_NEW
14176:
14177: IF l_del_tbl.FIRST IS NOT NULL THEN
14178: FORALL i in l_del_tbl.FIRST..l_del_tbl.LAST
14179: /*
14180: INDX,QP_PREQ_GRP.perform_grouping.upd1,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
14181: */
14182: --bug 14464004
14183: UPDATE /*+ index(qp_npreq_ldets_tmp QP_PREQ_LDETS_TMP_U1) */ qp_npreq_ldets_tmp --upd1
14184: SET PRICING_STATUS_CODE = G_DELETED_GRP,
14179: /*
14180: INDX,QP_PREQ_GRP.perform_grouping.upd1,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
14181: */
14182: --bug 14464004
14183: UPDATE /*+ index(qp_npreq_ldets_tmp QP_PREQ_LDETS_TMP_U1) */ qp_npreq_ldets_tmp --upd1
14184: SET PRICING_STATUS_CODE = G_DELETED_GRP,
14185: PRICING_STATUS_TEXT = 'Deleted in header+/-line grouping'
14186: WHERE line_detail_index = l_del_tbl(i);
14187:
14196:
14197: IF l_success_tbl.FIRST IS NOT NULL THEN
14198: FORALL i in l_success_tbl.FIRST..l_success_tbl.LAST
14199: /*
14200: INDX,QP_PREQ_GRP.perform_grouping.upd2,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
14201: */
14202: UPDATE qp_npreq_ldets_tmp --upd2
14203: SET PRICING_STATUS_CODE = G_STATUS_NEW
14204: WHERE line_detail_index = l_success_tbl(i);
14198: FORALL i in l_success_tbl.FIRST..l_success_tbl.LAST
14199: /*
14200: INDX,QP_PREQ_GRP.perform_grouping.upd2,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
14201: */
14202: UPDATE qp_npreq_ldets_tmp --upd2
14203: SET PRICING_STATUS_CODE = G_STATUS_NEW
14204: WHERE line_detail_index = l_success_tbl(i);
14205:
14206: /* FORALL i in l_success_tbl.FIRST..l_success_tbl.LAST
14393:
14394: IF l_del_tbl.FIRST IS NOT NULL THEN
14395: FORALL i in l_del_tbl.FIRST..l_del_tbl.LAST
14396: /*
14397: INDX,QP_PREQ_GRP.perform_grouping.upd3,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
14398: */
14399: UPDATE qp_npreq_ldets_tmp --upd3
14400: SET PRICING_STATUS_CODE = G_DELETED_GRP,
14401: PRICING_STATUS_TEXT = 'Deleted in pure line grouping'
14395: FORALL i in l_del_tbl.FIRST..l_del_tbl.LAST
14396: /*
14397: INDX,QP_PREQ_GRP.perform_grouping.upd3,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
14398: */
14399: UPDATE qp_npreq_ldets_tmp --upd3
14400: SET PRICING_STATUS_CODE = G_DELETED_GRP,
14401: PRICING_STATUS_TEXT = 'Deleted in pure line grouping'
14402: WHERE line_detail_index = l_del_tbl(i);
14403:
14422:
14423: IF l_success_tbl.FIRST IS NOT NULL THEN
14424: FORALL i in l_success_tbl.FIRST..l_success_tbl.LAST
14425: /*
14426: INDX,QP_PREQ_GRP.perform_grouping.upd4,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
14427: */
14428: UPDATE qp_npreq_ldets_tmp --upd4
14429: SET PRICING_STATUS_CODE = G_STATUS_NEW
14430: WHERE line_detail_index = l_success_tbl(i);
14424: FORALL i in l_success_tbl.FIRST..l_success_tbl.LAST
14425: /*
14426: INDX,QP_PREQ_GRP.perform_grouping.upd4,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
14427: */
14428: UPDATE qp_npreq_ldets_tmp --upd4
14429: SET PRICING_STATUS_CODE = G_STATUS_NEW
14430: WHERE line_detail_index = l_success_tbl(i);
14431:
14432: /* FORALL i in l_success_tbl.FIRST..l_success_tbl.LAST
14484: engine_debug('Getting Deleted in Product Grouping');
14485:
14486: END IF;
14487: /*
14488: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
14489: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
14490: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
14491: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
14492: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
14485:
14486: END IF;
14487: /*
14488: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
14489: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
14490: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
14491: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
14492: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
14493: */
14486: END IF;
14487: /*
14488: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
14489: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
14490: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
14491: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
14492: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
14493: */
14494: UPDATE qp_npreq_ldets_tmp --upd5
14487: /*
14488: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
14489: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
14490: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
14491: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
14492: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
14493: */
14494: UPDATE qp_npreq_ldets_tmp --upd5
14495: SET PRICING_STATUS_CODE = G_DELETED_GRP
14488: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
14489: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
14490: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
14491: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
14492: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
14493: */
14494: UPDATE qp_npreq_ldets_tmp --upd5
14495: SET PRICING_STATUS_CODE = G_DELETED_GRP
14496: WHERE PRICING_PHASE_ID = p_pricing_phase_id
14490: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
14491: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
14492: INDX,QP_PREQ_GRP.perform_grouping.upd5,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
14493: */
14494: UPDATE qp_npreq_ldets_tmp --upd5
14495: SET PRICING_STATUS_CODE = G_DELETED_GRP
14496: WHERE PRICING_PHASE_ID = p_pricing_phase_id
14497: AND PRICING_STATUS_CODE = G_STATUS_NEW
14498: AND CREATED_FROM_LIST_HEADER_ID = G_LIST_HEADER_ID_TBL_G(I)
14498: AND CREATED_FROM_LIST_HEADER_ID = G_LIST_HEADER_ID_TBL_G(I)
14499: AND CREATED_FROM_LIST_LINE_ID = G_LIST_LINE_ID_TBL_G(I)
14500: AND LINE_INDEX = G_LINE_INDEX_TBL_G(I);
14501:
14502: /* UPDATE qp_npreq_ldets_tmp
14503: SET PRICING_STATUS_CODE = G_DELETED_GRP
14504: WHERE CREATED_FROM_LIST_LINE_ID = G_LIST_LINE_ID_TBL_G(I)
14505: AND LINE_INDEX = G_LINE_INDEX_TBL_G(I); */
14506:
14526: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
14527: engine_debug('Getting Deleted in Product Grouping');
14528: END IF;
14529: /*
14530: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
14531: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
14532: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
14533: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
14534: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
14527: engine_debug('Getting Deleted in Product Grouping');
14528: END IF;
14529: /*
14530: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
14531: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
14532: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
14533: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
14534: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
14535: */
14528: END IF;
14529: /*
14530: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
14531: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
14532: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
14533: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
14534: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
14535: */
14536: UPDATE qp_npreq_ldets_tmp --upd6
14529: /*
14530: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
14531: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
14532: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
14533: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
14534: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
14535: */
14536: UPDATE qp_npreq_ldets_tmp --upd6
14537: SET PRICING_STATUS_CODE = G_DELETED_GRP
14530: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
14531: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
14532: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
14533: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
14534: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
14535: */
14536: UPDATE qp_npreq_ldets_tmp --upd6
14537: SET PRICING_STATUS_CODE = G_DELETED_GRP
14538: WHERE PRICING_PHASE_ID = p_pricing_phase_id
14532: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
14533: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_HEADER_ID,4
14534: INDX,QP_PREQ_GRP.perform_grouping.upd6,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
14535: */
14536: UPDATE qp_npreq_ldets_tmp --upd6
14537: SET PRICING_STATUS_CODE = G_DELETED_GRP
14538: WHERE PRICING_PHASE_ID = p_pricing_phase_id
14539: AND PRICING_STATUS_CODE = G_STATUS_NEW
14540: AND CREATED_FROM_LIST_HEADER_ID = G_LIST_HEADER_ID_TBL_G(I)
14540: AND CREATED_FROM_LIST_HEADER_ID = G_LIST_HEADER_ID_TBL_G(I)
14541: AND CREATED_FROM_LIST_LINE_ID = G_LIST_LINE_ID_TBL_G(I)
14542: AND LINE_INDEX = G_LINE_INDEX_TBL_G(I);
14543:
14544: /* UPDATE qp_npreq_ldets_tmp
14545: SET PRICING_STATUS_CODE = G_DELETED_GRP
14546: WHERE CREATED_FROM_LIST_LINE_ID = G_LIST_LINE_ID_TBL_G(I)
14547: AND LINE_INDEX = G_LINE_INDEX_TBL_G(I); */
14548:
14566: WHERE PRICING_STATUS_CODE = G_STATUS_NEW
14567: AND LINE_INDEX = nvl(p_line_index,LINE_INDEX);
14568:
14569: --Update all the line details records with pricing_status_code = 'T' to 'N'
14570: UPDATE qp_npreq_ldets_tmp a
14571: SET PRICING_STATUS_CODE = G_STATUS_NEW
14572: WHERE PRICING_PHASE_ID = p_pricing_phase_id
14573: AND PRICING_STATUS_CODE = G_STATUS_NEW
14574: AND LINE_INDEX = nvl(p_line_index,LINE_INDEX); */
16204: OR (QPLAT_Q.DERIVED_QUALIFIER_FLAG = 'Y' AND QPSQ.QUALIFY_HIER_DESCENDENTS_FLAG = 'Y')) -- Added for TCA
16205: ORDER BY 1,2;
16206:
16207: /*
16208: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
16209: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
16210: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
16211: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
16212: */
16205: ORDER BY 1,2;
16206:
16207: /*
16208: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
16209: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
16210: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
16211: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
16212: */
16213: CURSOR l_chk_deleted_pbh_cur(p_line_index IN PLS_INTEGER,
16206:
16207: /*
16208: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
16209: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
16210: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
16211: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
16212: */
16213: CURSOR l_chk_deleted_pbh_cur(p_line_index IN PLS_INTEGER,
16214: p_pricing_phase_id NUMBER) IS
16207: /*
16208: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
16209: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
16210: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
16211: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
16212: */
16213: CURSOR l_chk_deleted_pbh_cur(p_line_index IN PLS_INTEGER,
16214: p_pricing_phase_id NUMBER) IS
16215: SELECT /*+ INDEX(qp_npreq_ldets_tmp QP_PREQ_LDETS_TMP_N2) */ 'X' --bug 14464004
16211: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
16212: */
16213: CURSOR l_chk_deleted_pbh_cur(p_line_index IN PLS_INTEGER,
16214: p_pricing_phase_id NUMBER) IS
16215: SELECT /*+ INDEX(qp_npreq_ldets_tmp QP_PREQ_LDETS_TMP_N2) */ 'X' --bug 14464004
16216: FROM qp_npreq_ldets_tmp
16217: WHERE created_from_list_line_type = G_PRICE_BREAK_TYPE
16218: AND line_index = p_line_index
16219: AND pricing_phase_id = p_pricing_phase_id
16212: */
16213: CURSOR l_chk_deleted_pbh_cur(p_line_index IN PLS_INTEGER,
16214: p_pricing_phase_id NUMBER) IS
16215: SELECT /*+ INDEX(qp_npreq_ldets_tmp QP_PREQ_LDETS_TMP_N2) */ 'X' --bug 14464004
16216: FROM qp_npreq_ldets_tmp
16217: WHERE created_from_list_line_type = G_PRICE_BREAK_TYPE
16218: AND line_index = p_line_index
16219: AND pricing_phase_id = p_pricing_phase_id
16220: AND pricing_status_code <> G_STATUS_NEW;
16835: END LOOP; -- K
16836:
16837: end if; -- vivek, multi currency installed
16838:
16839: -- julin [3805113]: bulk insert into qp_npreq_ldets_tmp
16840: IF (D>1) THEN
16841: qp_debug_util.tstart('QP_NPREQ_LDETS_TMP','Inserting into ldets QP_NPREQ_LDETS_TMP');
16842: FORALL i in G_line_index_tbl.FIRST..G_line_index_tbl.LAST
16843: INSERT INTO qp_npreq_ldets_tmp
16837: end if; -- vivek, multi currency installed
16838:
16839: -- julin [3805113]: bulk insert into qp_npreq_ldets_tmp
16840: IF (D>1) THEN
16841: qp_debug_util.tstart('QP_NPREQ_LDETS_TMP','Inserting into ldets QP_NPREQ_LDETS_TMP');
16842: FORALL i in G_line_index_tbl.FIRST..G_line_index_tbl.LAST
16843: INSERT INTO qp_npreq_ldets_tmp
16844: (LINE_DETAIL_INDEX,
16845: LINE_DETAIL_TYPE_CODE,
16839: -- julin [3805113]: bulk insert into qp_npreq_ldets_tmp
16840: IF (D>1) THEN
16841: qp_debug_util.tstart('QP_NPREQ_LDETS_TMP','Inserting into ldets QP_NPREQ_LDETS_TMP');
16842: FORALL i in G_line_index_tbl.FIRST..G_line_index_tbl.LAST
16843: INSERT INTO qp_npreq_ldets_tmp
16844: (LINE_DETAIL_INDEX,
16845: LINE_DETAIL_TYPE_CODE,
16846: LINE_INDEX,
16847: CREATED_FROM_LIST_HEADER_ID,
16906: G_BREAK_UOM_tbl(i), /* Proration */
16907: G_BREAK_CONTEXT_tbl(i), /* Proration */
16908: G_BREAK_ATTRIBUTE_tbl(i) /* Proration */
16909: );
16910: qp_debug_util.tstop('QP_NPREQ_LDETS_TMP');
16911: END IF;
16912:
16913: IF (M>1) THEN
16914: qp_debug_util.tstart('INSERT_PRICE_LIST_ATTRS','Inserting into lin attrs INSERT_PRICE_LIST_ATTRS');
19488: AND LINE_TYPE_CODE <> G_ORDER_LEVEL
19489: AND (instr(PROCESS_STATUS,'FREEGOOD')=0); -- bug 2693700
19490:
19491: /*
19492: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
19493: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
19494: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
19495: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
19496: */
19489: AND (instr(PROCESS_STATUS,'FREEGOOD')=0); -- bug 2693700
19490:
19491: /*
19492: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
19493: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
19494: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
19495: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
19496: */
19497: CURSOR l_chk_deleted_pbh_cur(p_line_index IN PLS_INTEGER,
19490:
19491: /*
19492: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
19493: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
19494: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
19495: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
19496: */
19497: CURSOR l_chk_deleted_pbh_cur(p_line_index IN PLS_INTEGER,
19498: p_pricing_phase_id NUMBER) IS
19491: /*
19492: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
19493: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
19494: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
19495: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
19496: */
19497: CURSOR l_chk_deleted_pbh_cur(p_line_index IN PLS_INTEGER,
19498: p_pricing_phase_id NUMBER) IS
19499: SELECT/*+ INDEX(qp_npreq_ldets_tmp QP_PREQ_LDETS_TMP_N2) */ 'X' --bug 14464004
19495: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
19496: */
19497: CURSOR l_chk_deleted_pbh_cur(p_line_index IN PLS_INTEGER,
19498: p_pricing_phase_id NUMBER) IS
19499: SELECT/*+ INDEX(qp_npreq_ldets_tmp QP_PREQ_LDETS_TMP_N2) */ 'X' --bug 14464004
19500: FROM qp_npreq_ldets_tmp
19501: WHERE created_from_list_line_type = G_PRICE_BREAK_TYPE
19502: AND line_index = p_line_index
19503: AND pricing_phase_id = p_pricing_phase_id
19496: */
19497: CURSOR l_chk_deleted_pbh_cur(p_line_index IN PLS_INTEGER,
19498: p_pricing_phase_id NUMBER) IS
19499: SELECT/*+ INDEX(qp_npreq_ldets_tmp QP_PREQ_LDETS_TMP_N2) */ 'X' --bug 14464004
19500: FROM qp_npreq_ldets_tmp
19501: WHERE created_from_list_line_type = G_PRICE_BREAK_TYPE
19502: AND line_index = p_line_index
19503: AND pricing_phase_id = p_pricing_phase_id
19504: AND pricing_status_code <> G_STATUS_NEW;
29252:
29253: INDX,QP_PREQ_GRP.select_modifiers.l_excl_cur,QP_PRICING_ATTRIBUTES_N2,LIST_LINE_ID,1
29254: INDX,QP_PREQ_GRP.select_modifiers.l_excl_cur,QP_PRICING_ATTRIBUTES_N2,EXCLUDER_FLAG,2
29255:
29256: INDX,QP_PREQ_GRP.select_modifiers.l_excl_cur,qp_npreq_ldets_tmp_N2,PRICING_PHASE_ID,1
29257: INDX,QP_PREQ_GRP.select_modifiers.l_excl_cur,qp_npreq_ldets_tmp_N2,PRICING_STATUS_CODE,2
29258:
29259: */
29260:
29253: INDX,QP_PREQ_GRP.select_modifiers.l_excl_cur,QP_PRICING_ATTRIBUTES_N2,LIST_LINE_ID,1
29254: INDX,QP_PREQ_GRP.select_modifiers.l_excl_cur,QP_PRICING_ATTRIBUTES_N2,EXCLUDER_FLAG,2
29255:
29256: INDX,QP_PREQ_GRP.select_modifiers.l_excl_cur,qp_npreq_ldets_tmp_N2,PRICING_PHASE_ID,1
29257: INDX,QP_PREQ_GRP.select_modifiers.l_excl_cur,qp_npreq_ldets_tmp_N2,PRICING_STATUS_CODE,2
29258:
29259: */
29260:
29261: -- Delete Excluded Line Details Directly if the inner select returns an excluded row
29260:
29261: -- Delete Excluded Line Details Directly if the inner select returns an excluded row
29262:
29263: qp_debug_util.tstart('D_EXCLUDER','Update statement D_EXCLUDER');
29264: UPDATE qp_npreq_ldets_tmp a
29265: SET pricing_status_code = 'D_EXCLUDER'
29266: WHERE pricing_phase_id = p_pricing_phase_id
29267: AND pricing_status_code = G_STATUS_NEW
29268: AND EXISTS ( SELECT/*+ ORDERED USE_NL(c) index(c qp_preq_line_attrs_tmp_N2) index(b QP_PRICING_ATTRIBUTES_N2) l_excl_cur */
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
35035: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_check_modlist_cur,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
35036: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_check_modlist_cur,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
35037:
35038: INDX,lQP_PREQ_GRP.create_qualifier_from_list._check_modlist_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
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
35035: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_check_modlist_cur,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
35036: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_check_modlist_cur,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
35037:
35038: INDX,lQP_PREQ_GRP.create_qualifier_from_list._check_modlist_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
35039: INDX,lQP_PREQ_GRP.create_qualifier_from_list._check_modlist_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
35032:
35033: /*
35034: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_check_modlist_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
35035: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_check_modlist_cur,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,2
35036: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_check_modlist_cur,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,3
35037:
35038: INDX,lQP_PREQ_GRP.create_qualifier_from_list._check_modlist_cur,qp_npreq_line_attrs_tmp_N2,PRICING_STATUS_CODE,1
35039: INDX,lQP_PREQ_GRP.create_qualifier_from_list._check_modlist_cur,qp_npreq_line_attrs_tmp_N2,ATTRIBUTE_TYPE,2
35040: INDX,lQP_PREQ_GRP.create_qualifier_from_list._check_modlist_cur,qp_npreq_line_attrs_tmp_N2,CONTEXT,3
35044: */
35045: --check if passed in price list indeed has qualified
35046: CURSOR l_check_modlist_cur(p_line_index IN PLS_INTEGER) IS
35047: SELECT /*+ ORDERED index(qplat qp_preq_line_attrs_tmp_n2) l_check_modlist_cur */ 'X'
35048: FROM qp_npreq_ldets_tmp qpld,
35049: qp_npreq_line_attrs_tmp qplat
35050: WHERE qpld.line_index = p_line_index
35051: AND qpld.pricing_phase_id = G_PRICE_LIST_PHASE_ID
35052: AND qpld.pricing_status_code = G_STATUS_NEW
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,
35068: qp_npreq_line_attrs_tmp qplat
35069: WHERE qpld.line_index = qpll.line_index
35070: AND qpld.pricing_phase_id = G_PRICE_LIST_PHASE_ID
35071: AND qpld.pricing_status_code = G_STATUS_NEW
35094: AND line_index = p_line_index
35095: AND ROWNUM = 1; --just one record need to be returned
35096:
35097: /*
35098: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
35099: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
35100: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
35101: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
35102: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,PRICING_STATUS_CODE,5
35095: AND ROWNUM = 1; --just one record need to be returned
35096:
35097: /*
35098: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
35099: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
35100: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
35101: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
35102: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,PRICING_STATUS_CODE,5
35103: */
35096:
35097: /*
35098: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
35099: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
35100: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
35101: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
35102: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,PRICING_STATUS_CODE,5
35103: */
35104: CURSOR l_get_pl_cur(p_line_index IN PLS_INTEGER) IS
35097: /*
35098: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
35099: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
35100: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
35101: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
35102: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,PRICING_STATUS_CODE,5
35103: */
35104: CURSOR l_get_pl_cur(p_line_index IN PLS_INTEGER) IS
35105: SELECT created_from_list_header_id
35098: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
35099: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,PRICING_PHASE_ID,2
35100: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
35101: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
35102: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,PRICING_STATUS_CODE,5
35103: */
35104: CURSOR l_get_pl_cur(p_line_index IN PLS_INTEGER) IS
35105: SELECT created_from_list_header_id
35106: FROM qp_npreq_ldets_tmp
35102: INDX,QP_PREQ_GRP.create_qualifier_from_list.l_get_pl_cur,qp_npreq_ldets_tmp_N3,PRICING_STATUS_CODE,5
35103: */
35104: CURSOR l_get_pl_cur(p_line_index IN PLS_INTEGER) IS
35105: SELECT created_from_list_header_id
35106: FROM qp_npreq_ldets_tmp
35107: WHERE line_index = p_line_index
35108: and created_from_list_line_type in (G_PRICE_LIST_TYPE,G_BY_PBH) -- Bug#2882115
35109: and pricing_status_code = G_STATUS_NEW
35110: and pricing_phase_id = G_PRICE_LIST_PHASE_ID;
35324: x_status_code OUT NOCOPY VARCHAR2,
35325: x_status_text OUT NOCOPY VARCHAR2) AS
35326: /*
35327: -- Can use N1 instead of N3
35328: INDX,QP_PREQ_GRP.get_reason.l_reason_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
35329: */
35330: CURSOR l_reason_cur(p_line_index IN PLS_INTEGER) IS
35331: SELECT distinct pricing_status_code
35332: FROM qp_npreq_ldets_tmp
35328: INDX,QP_PREQ_GRP.get_reason.l_reason_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
35329: */
35330: CURSOR l_reason_cur(p_line_index IN PLS_INTEGER) IS
35331: SELECT distinct pricing_status_code
35332: FROM qp_npreq_ldets_tmp
35333: WHERE line_index = p_line_index
35334: AND pricing_status_code <> G_STATUS_NEW;
35335:
35336: J PLS_INTEGER:=0;
35407: ORDER BY PROCESSING_ORDER;
35408:
35409: --for the case where p_control_rec.price_flat = 'P' (G_PHASE)
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: */
35408:
35409: --for the case where p_control_rec.price_flat = 'P' (G_PHASE)
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,
35439: QPLT.GROUP_AMOUNT,
35440: QPLT.LINE_QUANTITY, --2388011_new
35441: QPLT.SELECTED_VOLUME_ATTR -- 2388011_new
35442: ,Nvl(QPLT.ROUNDING_FACTOR,QPLT.SELLING_ROUNDING_FACTOR) ROUNDING_FACTOR --10093463
35443: FROM qp_npreq_ldets_tmp QPLT
35444: WHERE QPLT.LINE_INDEX = L_LINE_INDEX
35445: AND QPLT.PRICING_STATUS_CODE = G_STATUS_NEW
35446: UNION
35447: -- hints added as temporary R12 fix for 5573442
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
35477: AND QPLT.CREATED_FROM_LIST_TYPE_CODE <> G_CHARGES_HEADER
35650:
35651: --For manual discount, we want pricing_group sequence to be NULL
35652: /*
35653: -- This update is not needed as it is taken care of by the setup
35654: INDX,QP_PREQ_GRP.call_calculation_engine.call_calculation_engine_upd1,qp_npreq_ldets_tmp_N6,AUTOMATIC_FLAG,1
35655: */
35656: /* UPDATE qp_npreq_ldets_tmp
35657: SET PRICING_GROUP_SEQUENCE = NULL
35658: WHERE AUTOMATIC_FLAG = G_NO; */
35652: /*
35653: -- This update is not needed as it is taken care of by the setup
35654: INDX,QP_PREQ_GRP.call_calculation_engine.call_calculation_engine_upd1,qp_npreq_ldets_tmp_N6,AUTOMATIC_FLAG,1
35655: */
35656: /* UPDATE qp_npreq_ldets_tmp
35657: SET PRICING_GROUP_SEQUENCE = NULL
35658: WHERE AUTOMATIC_FLAG = G_NO; */
35659:
35660:
36399: engine_debug ('In Line Detail Update Information1');
36400: END IF;
36401: IF (G_LINE_DETAIL_INDEX_TBL_D.COUNT > 0) THEN
36402: FORALL O IN G_LINE_DETAIL_INDEX_TBL_D.FIRST .. G_LINE_DETAIL_INDEX_TBL_D.LAST
36403: UPDATE qp_npreq_ldets_tmp
36404: SET ADJUSTMENT_AMOUNT = G_ADJUSTMENT_AMOUNT_TBL_D(O),
36405: --LINE_QUANTITY = G_QUALIFIER_VALUE_TBL_D(O),-- 2388011, ASK RAVI, either priced_quantity,group_quantity,group_amount
36406: BENEFIT_QTY = G_BENEFIT_QUANTITY_TBL_D(O),
36407: OPERAND_VALUE = G_OPERAND_VALUE_TBL_D(O) --2388011_new, remove comma
36416: END IF;
36417: -- Update All the Line Details Information at 1 Shot for all Lines
36418: IF (G_LINE_DETAIL_INDEX_TBL_D.COUNT > 0) THEN
36419: FORALL O IN G_LINE_DETAIL_INDEX_TBL_D.FIRST .. G_LINE_DETAIL_INDEX_TBL_D.LAST
36420: UPDATE /*+ index(QP_NPREQ_LDETS_TMP QP_PREQ_LDETS_TMP_U1) */ qp_npreq_ldets_tmp --5658579
36421: SET ADJUSTMENT_AMOUNT = G_ADJUSTMENT_AMOUNT_TBL_D(O),
36422: --LINE_QUANTITY = G_QUALIFIER_VALUE_TBL_D(O), -- 2388011, ASK RAVI, either priced_quantity,group_quantity,group_amount
36423: BENEFIT_QTY = G_BENEFIT_QUANTITY_TBL_D(O),
36424: OPERAND_VALUE = G_OPERAND_VALUE_TBL_D(O),
36435: END IF;
36436: -- Update All the Related Line Details Information at 1 Shot for all Lines
36437: IF (G_LINE_DETAIL_INDEX_TBL_R.COUNT > 0) THEN
36438: FORALL O IN G_LINE_DETAIL_INDEX_TBL_R.FIRST .. G_LINE_DETAIL_INDEX_TBL_R.LAST
36439: UPDATE qp_npreq_ldets_tmp
36440: SET ADJUSTMENT_AMOUNT = G_ADJUSTMENT_AMOUNT_TBL_R(O),
36441: LINE_QUANTITY = G_LINE_QUANTITY_TBL_R(O)
36442: WHERE LINE_DETAIL_INDEX = G_LINE_DETAIL_INDEX_TBL_R(O);
36443: END IF;
36507: x_status_code OUT NOCOPY VARCHAR2,
36508: x_status_text OUT NOCOPY VARCHAR2 ) AS
36509:
36510: /*
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
36508: x_status_text OUT NOCOPY VARCHAR2 ) AS
36509:
36510: /*
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: */
36509:
36510: /*
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
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;
37050: IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
37051: -- OR l_formula_based_price IS NOT NULL) THEN
37052: IF (i.OPERAND_CALCULATION_CODE in ('UNIT_PRICE','LIST_PRICE')) THEN
37053: /*
37054: INDX,QP_PREQ_GRP.formula_processing.upd1,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
37055: INDX,QP_PREQ_GRP.formula_processing.upd1,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
37056: INDX,QP_PREQ_GRP.formula_processing.upd1,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
37057: */
37058: UPDATE qp_npreq_ldets_tmp --upd1
37051: -- OR l_formula_based_price IS NOT NULL) THEN
37052: IF (i.OPERAND_CALCULATION_CODE in ('UNIT_PRICE','LIST_PRICE')) THEN
37053: /*
37054: INDX,QP_PREQ_GRP.formula_processing.upd1,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
37055: INDX,QP_PREQ_GRP.formula_processing.upd1,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
37056: INDX,QP_PREQ_GRP.formula_processing.upd1,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
37057: */
37058: UPDATE qp_npreq_ldets_tmp --upd1
37059: SET OPERAND_VALUE = l_formula_based_price
37052: IF (i.OPERAND_CALCULATION_CODE in ('UNIT_PRICE','LIST_PRICE')) THEN
37053: /*
37054: INDX,QP_PREQ_GRP.formula_processing.upd1,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
37055: INDX,QP_PREQ_GRP.formula_processing.upd1,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
37056: INDX,QP_PREQ_GRP.formula_processing.upd1,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
37057: */
37058: UPDATE qp_npreq_ldets_tmp --upd1
37059: SET OPERAND_VALUE = l_formula_based_price
37060: WHERE CREATED_FROM_LIST_LINE_ID = i.CREATED_FROM_LIST_LINE_ID
37054: INDX,QP_PREQ_GRP.formula_processing.upd1,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
37055: INDX,QP_PREQ_GRP.formula_processing.upd1,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
37056: INDX,QP_PREQ_GRP.formula_processing.upd1,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
37057: */
37058: UPDATE qp_npreq_ldets_tmp --upd1
37059: SET OPERAND_VALUE = l_formula_based_price
37060: WHERE CREATED_FROM_LIST_LINE_ID = i.CREATED_FROM_LIST_LINE_ID
37061: AND PRICING_PHASE_ID = p_pricing_phase_id
37062: AND LINE_INDEX = i.line_index
37071: PRICING_STATUS_TEXT ='Priced by formula'
37072: WHERE LINE_INDEX = i.line_index;
37073: ELSIF (i.OPERAND_CALCULATION_CODE = 'PERCENT_PRICE') THEN
37074: /*
37075: INDX,QP_PREQ_GRP.formula_processing.upd3,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
37076: INDX,QP_PREQ_GRP.formula_processing.upd3,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
37077: INDX,QP_PREQ_GRP.formula_processing.upd3,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
37078: */
37079: UPDATE qp_npreq_ldets_tmp --upd3
37072: WHERE LINE_INDEX = i.line_index;
37073: ELSIF (i.OPERAND_CALCULATION_CODE = 'PERCENT_PRICE') THEN
37074: /*
37075: INDX,QP_PREQ_GRP.formula_processing.upd3,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
37076: INDX,QP_PREQ_GRP.formula_processing.upd3,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
37077: INDX,QP_PREQ_GRP.formula_processing.upd3,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
37078: */
37079: UPDATE qp_npreq_ldets_tmp --upd3
37080: SET OPERAND_VALUE = l_formula_based_price
37073: ELSIF (i.OPERAND_CALCULATION_CODE = 'PERCENT_PRICE') THEN
37074: /*
37075: INDX,QP_PREQ_GRP.formula_processing.upd3,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
37076: INDX,QP_PREQ_GRP.formula_processing.upd3,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
37077: INDX,QP_PREQ_GRP.formula_processing.upd3,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
37078: */
37079: UPDATE qp_npreq_ldets_tmp --upd3
37080: SET OPERAND_VALUE = l_formula_based_price
37081: WHERE CREATED_FROM_LIST_LINE_ID = i.CREATED_FROM_LIST_LINE_ID
37075: INDX,QP_PREQ_GRP.formula_processing.upd3,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
37076: INDX,QP_PREQ_GRP.formula_processing.upd3,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
37077: INDX,QP_PREQ_GRP.formula_processing.upd3,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
37078: */
37079: UPDATE qp_npreq_ldets_tmp --upd3
37080: SET OPERAND_VALUE = l_formula_based_price
37081: WHERE CREATED_FROM_LIST_LINE_ID = i.CREATED_FROM_LIST_LINE_ID
37082: AND PRICING_PHASE_ID = p_pricing_phase_id
37083: AND LINE_INDEX = i.line_index
37092: PRICING_STATUS_TEXT ='Priced by formula'
37093: WHERE LINE_INDEX = i.line_index;
37094: ELSE -- BLOCK_PRICE, % , AMT , NEWPRICE
37095: /*
37096: INDX,QP_PREQ_GRP.formula_processing.upd5,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
37097: INDX,QP_PREQ_GRP.formula_processing.upd5,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
37098: INDX,QP_PREQ_GRP.formula_processing.upd5,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
37099: */
37100: UPDATE qp_npreq_ldets_tmp --upd5
37093: WHERE LINE_INDEX = i.line_index;
37094: ELSE -- BLOCK_PRICE, % , AMT , NEWPRICE
37095: /*
37096: INDX,QP_PREQ_GRP.formula_processing.upd5,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
37097: INDX,QP_PREQ_GRP.formula_processing.upd5,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
37098: INDX,QP_PREQ_GRP.formula_processing.upd5,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
37099: */
37100: UPDATE qp_npreq_ldets_tmp --upd5
37101: SET OPERAND_VALUE = l_formula_based_price
37094: ELSE -- BLOCK_PRICE, % , AMT , NEWPRICE
37095: /*
37096: INDX,QP_PREQ_GRP.formula_processing.upd5,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
37097: INDX,QP_PREQ_GRP.formula_processing.upd5,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
37098: INDX,QP_PREQ_GRP.formula_processing.upd5,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
37099: */
37100: UPDATE qp_npreq_ldets_tmp --upd5
37101: SET OPERAND_VALUE = l_formula_based_price
37102: WHERE CREATED_FROM_LIST_LINE_ID = i.CREATED_FROM_LIST_LINE_ID
37096: INDX,QP_PREQ_GRP.formula_processing.upd5,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
37097: INDX,QP_PREQ_GRP.formula_processing.upd5,qp_npreq_ldets_tmp_N1,PRICING_STATUS_CODE,2
37098: INDX,QP_PREQ_GRP.formula_processing.upd5,qp_npreq_ldets_tmp_N1,PRICING_PHASE_ID,3
37099: */
37100: UPDATE qp_npreq_ldets_tmp --upd5
37101: SET OPERAND_VALUE = l_formula_based_price
37102: WHERE CREATED_FROM_LIST_LINE_ID = i.CREATED_FROM_LIST_LINE_ID
37103: AND PRICING_PHASE_ID = p_pricing_phase_id
37104: AND LINE_INDEX = i.line_index
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;
37198: --Delete From qp_nformula_step_values_tmp;
37199: --Delete From QP_PREQ_LINE_ATTRS_FORMULA_TMP;
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);
37210: END IF;
37211: select count(*) into l_count from qp_npreq_ldets_tmp;
37212: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
37213: engine_debug('qp_npreq_ldets_tmp '||l_count);
37214: END IF;
37215: END IF;
37209: engine_debug('qp_npreq_line_attrs_tmp '||l_count);
37210: END IF;
37211: select count(*) into l_count from qp_npreq_ldets_tmp;
37212: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
37213: engine_debug('qp_npreq_ldets_tmp '||l_count);
37214: END IF;
37215: END IF;
37216: --added by yangli for Java Engine project
37217: ELSE
37283: );
37284:
37285: /*
37286: -- Can use N2 instead of N4
37287: INDX,QP_PREQ_GRP.set_order_level_rltd.l_line_detail_index_cur,qp_npreq_ldets_tmp_N2,PRICING_PHASE_ID,1
37288: INDX,QP_PREQ_GRP.set_order_level_rltd.l_line_detail_index_cur,qp_npreq_ldets_tmp_N2,PRICING_STATUS_CODE,2
37289: */
37290: CURSOR l_line_detail_index_cur is
37291: SELECT line_detail_index
37284:
37285: /*
37286: -- Can use N2 instead of N4
37287: INDX,QP_PREQ_GRP.set_order_level_rltd.l_line_detail_index_cur,qp_npreq_ldets_tmp_N2,PRICING_PHASE_ID,1
37288: INDX,QP_PREQ_GRP.set_order_level_rltd.l_line_detail_index_cur,qp_npreq_ldets_tmp_N2,PRICING_STATUS_CODE,2
37289: */
37290: CURSOR l_line_detail_index_cur is
37291: SELECT line_detail_index
37292: FROM qp_npreq_ldets_tmp
37288: INDX,QP_PREQ_GRP.set_order_level_rltd.l_line_detail_index_cur,qp_npreq_ldets_tmp_N2,PRICING_STATUS_CODE,2
37289: */
37290: CURSOR l_line_detail_index_cur is
37291: SELECT line_detail_index
37292: FROM qp_npreq_ldets_tmp
37293: WHERE PRICING_STATUS_CODE = G_STATUS_NEW
37294: AND PRICING_PHASE_ID = p_pricing_phase_id
37295: AND MODIFIER_LEVEL_CODE = G_ORDER_LEVEL;
37296:
39469:
39470: x_status_code := FND_API.G_RET_STS_SUCCESS;
39471:
39472: /*
39473: Populating table qp_npreq_ldets_tmp with details of all selected modifier
39474: lines after Pattern matching.
39475: */
39476:
39477: l_start_line_index := G_MIN_LINE_INDEX;
39481:
39482: FOR i IN 1..l_cnt_iter
39483: LOOP
39484:
39485: INSERT INTO qp_npreq_ldets_tmp
39486: (LINE_DETAIL_INDEX,
39487: LINE_DETAIL_TYPE_CODE,
39488: PRICE_BREAK_TYPE_CODE,
39489: LINE_INDEX,
39615: qp_debug_util.tstop('POPULATE_PAT_TEMP_TABLES_QR1');
39616:
39617:
39618: IF qp_preq_grp.G_DEBUG_ENGINE = FND_API.G_TRUE THEN
39619: qp_preq_grp.engine_debug('Successfully inserted data in table qp_npreq_ldets_tmp');
39620: END IF;
39621:
39622: qp_debug_util.tstart('POPULATE_PAT_TEMP_TABLES_QR3','Populate_Pat_Temp_Tables Inserting line attrs temp');
39623:
39697: qpldets.MODIFIER_LEVEL_CODE, --MODIFIER_LEVEL_CODE
39698: qpldets.PRIMARY_UOM_FLAG, --PRIMARY_UOM_FLAG
39699: --qppatr.PRODUCT_SEGMENT_ID, --SEGMENT_ID
39700: qplaptr.derived_qualifier_flag
39701: FROM qp_npreq_ldets_tmp qpldets,
39702: QP_PRICING_ATTRIBUTES qppatr,
39703: qp_npreq_line_attrs_tmp qplaptr
39704: WHERE qpldets.pricing_phase_id = p_pricing_phase_id
39705: AND qpldets.line_index BETWEEN l_start_line_index AND l_end_line_index
39746: qpldets.MODIFIER_LEVEL_CODE, --MODIFIER_LEVEL_CODE
39747: qpldets.PRIMARY_UOM_FLAG, --PRIMARY_UOM_FLAG
39748: --qppatr.PRICING_SEGMENT_ID, --SEGMENT_ID
39749: qplaptr.derived_qualifier_flag
39750: FROM qp_npreq_ldets_tmp qpldets,
39751: QP_PRICING_ATTRIBUTES qppatr,
39752: qp_npreq_line_attrs_tmp qplaptr
39753: WHERE qpldets.pricing_phase_id = p_pricing_phase_id
39754: AND qpldets.line_index BETWEEN l_start_line_index AND l_end_line_index
39882: qpldets.PRIMARY_UOM_FLAG, -- PRIMARY_UOM_FLAG
39883: --qpq.SEGMENT_ID,
39884: qplines.derived_qualifier_flag
39885: FROM
39886: qp_npreq_ldets_tmp qpldets,
39887: QP_PREQ_PATRN_QUAL_TMP qppatq,
39888: qp_qualifiers qpq,
39889: qp_npreq_line_attrs_tmp qplines
39890: WHERE
39934: qpldets.PRIMARY_UOM_FLAG, -- PRIMARY_UOM_FLAG
39935: --qpq.SEGMENT_ID,
39936: qplines.derived_qualifier_flag
39937: FROM
39938: qp_npreq_ldets_tmp qpldets,
39939: QP_PREQ_PATRN_QUAL_TMP qppatq,
39940: qp_qualifiers qpq,
39941: qp_npreq_line_attrs_tmp qplines
39942: WHERE
40046: qpldets.PRIMARY_UOM_FLAG, -- PRIMARY_UOM_FLAG
40047: --qpq.SEGMENT_ID,
40048: qplines.derived_qualifier_flag
40049: FROM
40050: qp_npreq_ldets_tmp qpldets,
40051: QP_PREQ_PATRN_QUAL_TMP qppatq,
40052: qp_qualifiers qpq,
40053: qp_npreq_line_attrs_tmp qplines
40054: WHERE
40097: qpldets.PRIMARY_UOM_FLAG, -- PRIMARY_UOM_FLAG
40098: --qpq.SEGMENT_ID,
40099: qplines.derived_qualifier_flag
40100: FROM
40101: qp_npreq_ldets_tmp qpldets,
40102: QP_PREQ_PATRN_QUAL_TMP qppatq,
40103: qp_qualifiers qpq,
40104: qp_npreq_line_attrs_tmp qplines
40105: WHERE
40135: -- and storing sourced value for the pricing volume attribute used in the line.
40136: -- Group of line modifiers will not be considered here. Line quantity will be
40137: -- updated later in evaluate_between procedure for them.
40138:
40139: UPDATE qp_npreq_ldets_tmp a
40140: SET a.line_quantity = ( SELECT VALUE_FROM
40141: FROM qp_npreq_line_attrs_tmp b
40142: WHERE b.LINE_INDEX = a.LINE_INDEX
40143: AND b.LINE_DETAIL_INDEX = a.LINE_DETAIL_INDEX
40925: ORDER BY QPP.PHASE_SEQUENCE,SEARCH_FLAG DESC; */
40926:
40927: /*
40928: Can use N2 instead of N4
40929: INDX,QP_PREQ_GRP.price_request.l_get_line_index_cur,qp_npreq_ldets_tmp_N2,PRICING_PHASE_ID,1
40930: INDX,QP_PREQ_GRP.price_request.l_get_line_index_cur,qp_npreq_ldets_tmp_N2,PRICING_STATUS_CODE,2
40931: */
40932: CURSOR l_get_line_index_cur(p_pricing_phase_id NUMBER) IS
40933: SELECT DISTINCT LINE_INDEX
40926:
40927: /*
40928: Can use N2 instead of N4
40929: INDX,QP_PREQ_GRP.price_request.l_get_line_index_cur,qp_npreq_ldets_tmp_N2,PRICING_PHASE_ID,1
40930: INDX,QP_PREQ_GRP.price_request.l_get_line_index_cur,qp_npreq_ldets_tmp_N2,PRICING_STATUS_CODE,2
40931: */
40932: CURSOR l_get_line_index_cur(p_pricing_phase_id NUMBER) IS
40933: SELECT DISTINCT LINE_INDEX
40934: FROM qp_npreq_ldets_tmp
40930: INDX,QP_PREQ_GRP.price_request.l_get_line_index_cur,qp_npreq_ldets_tmp_N2,PRICING_STATUS_CODE,2
40931: */
40932: CURSOR l_get_line_index_cur(p_pricing_phase_id NUMBER) IS
40933: SELECT DISTINCT LINE_INDEX
40934: FROM qp_npreq_ldets_tmp
40935: WHERE PRICING_PHASE_ID = p_pricing_phase_id
40936: AND PRICING_STATUS_CODE = G_STATUS_NEW;
40937:
40938: /*
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
40976: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
40977: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
40978: */
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
40976: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
40977: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
40978: */
40979: CURSOR l_chk_deleted_pbh_cur(p_line_index IN PLS_INTEGER,
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
40976: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
40977: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
40978: */
40979: CURSOR l_chk_deleted_pbh_cur(p_line_index IN PLS_INTEGER,
40980: p_pricing_phase_id NUMBER) IS
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
40976: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,ASK_FOR_FLAG,3
40977: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
40978: */
40979: CURSOR l_chk_deleted_pbh_cur(p_line_index IN PLS_INTEGER,
40980: p_pricing_phase_id NUMBER) IS
40981: SELECT /*+ INDEX(qp_npreq_ldets_tmp QP_PREQ_LDETS_TMP_N2) */ 'X' --bug 14464004
40977: INDX,QP_PREQ_GRP.delete_invalid_pbh_children.l_check_deleted_pbh_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,4
40978: */
40979: CURSOR l_chk_deleted_pbh_cur(p_line_index IN PLS_INTEGER,
40980: p_pricing_phase_id NUMBER) IS
40981: SELECT /*+ INDEX(qp_npreq_ldets_tmp QP_PREQ_LDETS_TMP_N2) */ 'X' --bug 14464004
40982: FROM qp_npreq_ldets_tmp
40983: WHERE created_from_list_line_type = G_PRICE_BREAK_TYPE
40984: AND line_index = p_line_index
40985: AND pricing_phase_id = p_pricing_phase_id
40978: */
40979: CURSOR l_chk_deleted_pbh_cur(p_line_index IN PLS_INTEGER,
40980: p_pricing_phase_id NUMBER) IS
40981: SELECT /*+ INDEX(qp_npreq_ldets_tmp QP_PREQ_LDETS_TMP_N2) */ 'X' --bug 14464004
40982: FROM qp_npreq_ldets_tmp
40983: WHERE created_from_list_line_type = G_PRICE_BREAK_TYPE
40984: AND line_index = p_line_index
40985: AND pricing_phase_id = p_pricing_phase_id
40986: AND pricing_status_code <> G_STATUS_NEW;
41068: AND trunc(sysdate) BETWEEN trunc(nvl(a.START_DATE_ACTIVE,sysdate))
41069: AND trunc(nvl(a.END_DATE_ACTIVE,sysdate));
41070:
41071: /*
41072: INDX,QP_PREQ_GRP.price_request.l_get_max_line_detail_index,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
41073: */
41074: CURSOR l_get_max_line_detail_index IS
41075: SELECT MAX(LINE_DETAIL_INDEX)
41076: FROM qp_npreq_ldets_tmp;
41072: INDX,QP_PREQ_GRP.price_request.l_get_max_line_detail_index,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
41073: */
41074: CURSOR l_get_max_line_detail_index IS
41075: SELECT MAX(LINE_DETAIL_INDEX)
41076: FROM qp_npreq_ldets_tmp;
41077:
41078: /*
41079: INDX,QP_PREQ_GRP.price_request.l_limit_check_cur,qp_npreq_ldets_tmp_N4,HEADER_LIMIT_EXISTS,1
41080: INDX,QP_PREQ_GRP.price_request.l_limit_check_cur,qp_npreq_ldets_tmp_N4,PRICING_STATUS_CODE,2
41075: SELECT MAX(LINE_DETAIL_INDEX)
41076: FROM qp_npreq_ldets_tmp;
41077:
41078: /*
41079: INDX,QP_PREQ_GRP.price_request.l_limit_check_cur,qp_npreq_ldets_tmp_N4,HEADER_LIMIT_EXISTS,1
41080: INDX,QP_PREQ_GRP.price_request.l_limit_check_cur,qp_npreq_ldets_tmp_N4,PRICING_STATUS_CODE,2
41081: */
41082: CURSOR l_limit_check_cur IS
41083: SELECT HEADER_LIMIT_EXISTS,LINE_LIMIT_EXISTS
41076: FROM qp_npreq_ldets_tmp;
41077:
41078: /*
41079: INDX,QP_PREQ_GRP.price_request.l_limit_check_cur,qp_npreq_ldets_tmp_N4,HEADER_LIMIT_EXISTS,1
41080: INDX,QP_PREQ_GRP.price_request.l_limit_check_cur,qp_npreq_ldets_tmp_N4,PRICING_STATUS_CODE,2
41081: */
41082: CURSOR l_limit_check_cur IS
41083: SELECT HEADER_LIMIT_EXISTS,LINE_LIMIT_EXISTS
41084: FROM qp_npreq_ldets_tmp
41080: INDX,QP_PREQ_GRP.price_request.l_limit_check_cur,qp_npreq_ldets_tmp_N4,PRICING_STATUS_CODE,2
41081: */
41082: CURSOR l_limit_check_cur IS
41083: SELECT HEADER_LIMIT_EXISTS,LINE_LIMIT_EXISTS
41084: FROM qp_npreq_ldets_tmp
41085: WHERE PRICING_STATUS_CODE = G_STATUS_NEW
41086: AND HEADER_LIMIT_EXISTS = G_YES ;
41087:
41088: /*
41774:
41775: --Put back the changes for bug 2457983
41776: --in OC case where caller passes adjustments, the adjustments are inserted
41777: --but G_LINE_DETAIL_INDEX is not incremented which causes a
41778: --unique constraint violation on qp_npreq_ldets_tmp_U1
41779: --hence need to fetch the maximum line_detail_index
41780: G_LINE_DETAIL_INDEX := nvl(l_max_line_detail_index,1);
41781: IF G_DEBUG_ENGINE = FND_API.G_TRUE THEN
41782: engine_debug('G_LINE_DETAIL_INDEX '||G_LINE_DETAIL_INDEX);