[Home] [Help]
630: for cl in
631: (select line.line_index, ldet.line_detail_index,
632: ldet.created_from_list_line_id, ldet.modifier_level_code,
633: line.priced_quantity, line.parent_quantity, line.unit_price
634: from qp_npreq_lines_tmp line, qp_npreq_ldets_tmp ldet
635: where line.line_index = l_line_index
636: and line.pricing_status_code in (G_STATUS_NEW, G_STATUS_UPDATED)
637: and line.price_flag in (G_YES, G_PHASE)
638: and line.line_type_code = G_LINE_LEVEL
696: --for svc lines and volattrval for non-svc lines
697: --this lumpsum_qty will be used to prorate the lumpsum adj_amt
698: for cl1 in (select line.line_index, lattr.value_from,
699: line.parent_quantity, ldet.line_detail_index
700: from qp_npreq_ldets_tmp ldet,
701: qp_npreq_lines_tmp line,
702: qp_npreq_line_attrs_tmp lattr
703: where ldet.created_from_list_line_id = cl.created_from_list_line_id
704: and ldet.pricing_status_code = G_STATUS_NEW
768: and lattr.line_index = cl.line_index
769: and lattr.context = QP_PREQ_GRP.G_PRIC_VOLUME_CONTEXT
770: and lattr.attribute = ldetattr.attribute
771: and exists (select 'Y'
772: from qp_npreq_ldets_tmp ldet
773: where ldet.created_from_list_line_id = cl.created_from_list_line_id
774: and ldet.modifier_level_code = QP_PREQ_GRP.G_LINE_GROUP
775: and ldet.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW))
776: loop
1130: line.catchweight_qty,
1131: line.line_quantity ordered_qty
1132: -- line.unit_price,
1133: -- line.adjusted_unit_price
1134: FROM qp_npreq_ldets_tmp ldet,
1135: qp_npreq_lines_tmp line
1136: WHERE ldet.pricing_phase_id > 1
1137: AND ldet.pricing_status_code = G_STATUS_NEW
1138: AND ldet.automatic_flag = G_NO
1226: END LOOP; --j
1227: END IF; --l_debug
1228:
1229: FORALL i IN l_ordqty_line_dtl_index.FIRST..l_ordqty_line_dtl_index.LAST
1230: UPDATE qp_npreq_ldets_tmp SET order_qty_operand = l_ordqty_ord_qty_operand(i)
1231: WHERE line_detail_index = l_ordqty_line_dtl_index(i);
1232: END IF; --l_ordqty_line_dtl_index.COUNT
1233:
1234: IF (l_debug = FND_API.G_TRUE) THEN
1303: END IF;
1304: IF l_header_id IS NOT NULL THEN
1305: BEGIN
1306: SELECT G_YES INTO l_exist_changed_order_adj
1307: FROM qp_npreq_ldets_tmp ldet
1308: WHERE ldet.process_code IN (G_STATUS_UPDATED, G_STATUS_NEW)
1309: AND ldet.pricing_status_code = G_STATUS_NEW
1310: AND ldet.modifier_level_code = G_ORDER_LEVEL
1311: AND ldet.applied_flag = G_YES;
1331: QP_PREQ_GRP.engine_debug('checking header adj operand changes');
1332: END IF;
1333: BEGIN
1334: SELECT SUM(operand_value) INTO l_ldet_sum_operand
1335: FROM qp_npreq_ldets_tmp ldet
1336: WHERE ldet.modifier_level_code = G_ORDER_LEVEL
1337: AND ldet.pricing_status_code = G_STATUS_NEW
1338: AND ldet.automatic_flag = G_YES
1339: AND applied_flag = G_YES;
1392: --if there is a summary line, make sure there are applied adj against it
1393: IF l_header_id IS NOT NULL THEN
1394: BEGIN
1395: SELECT G_YES INTO l_exist_changed_order_adj
1396: FROM qp_npreq_ldets_tmp ldet
1397: WHERE ldet.pricing_status_code = G_STATUS_NEW
1398: AND ldet.modifier_level_code = G_ORDER_LEVEL
1399: AND ldet.applied_flag = G_YES;
1400: EXCEPTION
1418: QP_PREQ_GRP.engine_debug('checking header adj operand changes');
1419: END IF;
1420: BEGIN
1421: SELECT SUM(operand_value) INTO l_ldet_sum_operand
1422: FROM qp_npreq_ldets_tmp ldet
1423: WHERE ldet.modifier_level_code = G_ORDER_LEVEL
1424: AND ldet.pricing_status_code = G_STATUS_NEW
1425: AND ldet.automatic_flag = G_YES
1426: AND applied_flag = G_YES;
1430: END;
1431:
1432: BEGIN
1433: SELECT SUM(operand_value) INTO l_adj_sum_operand
1434: FROM qp_npreq_ldets_tmp ldet
1435: WHERE ldet.modifier_level_code = G_ORDER_LEVEL
1436: AND ldet.pricing_status_code = G_STATUS_UNCHANGED
1437: AND ldet.automatic_flag = G_YES
1438: AND applied_flag = G_YES;
2775: ldet.pricing_group_sequence,
2776: nvl(ldet.line_quantity,
2777: nvl(line.priced_quantity, line.line_quantity))
2778: FROM
2779: qp_npreq_ldets_tmp ldet,
2780: qp_npreq_ldets_tmp ldet_pbh,
2781: qp_npreq_lines_tmp line,
2782: qp_pricing_attributes qpa
2783: WHERE
2776: nvl(ldet.line_quantity,
2777: nvl(line.priced_quantity, line.line_quantity))
2778: FROM
2779: qp_npreq_ldets_tmp ldet,
2780: qp_npreq_ldets_tmp ldet_pbh,
2781: qp_npreq_lines_tmp line,
2782: qp_pricing_attributes qpa
2783: WHERE
2784: ldet.line_detail_index = rltd.related_line_detail_index
2789: AND ldet.updated_flag = G_YES
2790: AND rltd.relationship_type_code = G_PBH_LINE
2791: AND rltd.pricing_status_code = G_STATUS_NEW)
2792: WHERE rltd.line_detail_index IN (SELECT ldet.line_detail_index
2793: FROM qp_npreq_ldets_tmp ldet
2794: WHERE ldet.pricing_status_code = G_STATUS_UNCHANGED
2795: AND ldet.applied_flag = G_YES
2796: AND ldet.updated_flag = G_YES
2797: AND ldet.created_from_list_line_type = G_PRICE_BREAK_TYPE)
2803: || SQL%ROWCOUNT);
2804: QP_PREQ_GRP.engine_debug('Updating the child detail type code');
2805: END IF;
2806:
2807: UPDATE qp_npreq_ldets_tmp
2808: SET line_detail_type_code = QP_PREQ_PUB.G_CHILD_DETAIL_TYPE
2809: WHERE line_detail_index IN (SELECT related_line_detail_index
2810: FROM qp_npreq_rltd_lines_tmp
2811: WHERE relationship_type_code = G_PBH_LINE
2812: AND pricing_status_code = G_STATUS_NEW);
2813:
2814: IF (l_debug = FND_API.G_TRUE) THEN
2815: FOR i IN (SELECT line_detail_index
2816: FROM qp_npreq_ldets_tmp
2817: WHERE line_detail_type_code = QP_PREQ_PUB.G_CHILD_DETAIL_TYPE)
2818: LOOP
2819: QP_PREQ_GRP.engine_debug(' PBH Child Line Detail Index : ' || i.line_detail_index);
2820: END LOOP;
3114: decode(ldet.calculation_code, 'BACK_CALCULATE', 10000,
3115: decode(ldet.created_from_list_line_type, 'DIS',
3116: decode(ldet.applied_flag, G_YES, - 1000, - 100),
3117: decode(ldet.applied_flag, G_YES, 100, 1000)))) precedence
3118: FROM qp_npreq_ldets_tmp ldet, qp_npreq_lines_tmp line
3119: WHERE line.line_index = p_line_index
3120: AND line.line_type_code = G_LINE_LEVEL -- sql repos
3121: AND line.price_flag IN (G_YES, G_PHASE)
3122: AND ldet.line_index = line.line_index
3606: --changed this for bug 2776800 to populate order_uom_selling_price
3607: --this means there are no adjustments and calculation has not taken place
3608: or line.order_uom_selling_price IS NULL )
3609: and not exists (select ldet.line_index
3610: from qp_npreq_ldets_tmp ldet
3611: where (ldet.line_index = line.line_index
3612: or ldet.modifier_level_code = G_ORDER_LEVEL)
3613: and nvl(ldet.created_from_list_type_code,'NULL') not in
3614: (G_PRICE_LIST_HEADER,G_AGR_LIST_HEADER)
4026: --added for bug 2926554
4027: IF l_ldet_dtl_index.COUNT > 0
4028: THEN
4029: FORALL i IN l_ldet_dtl_index.first..l_ldet_dtl_index.last
4030: UPDATE qp_npreq_ldets_tmp ldet
4031: SET ldet.operand_value = l_ldet_operand_value(i)
4032: , ldet.adjustment_amount = l_ldet_adjamt(i)
4033: , ldet.applied_flag = l_ldet_applied_flag(i)
4034: , ldet.updated_flag = l_ldet_updated_flag(i)
4105: AND (line.price_flag IN (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
4106: OR nvl(line.processed_code, '0') = QP_PREQ_PUB.G_BY_ENGINE)
4107: AND (G_CALCULATE_FLAG = G_CALCULATE_ONLY
4108:
4109: OR EXISTS (SELECT 'Y' FROM qp_npreq_ldets_tmp ldet
4110: WHERE ldet.line_index = line.line_index
4111: AND ldet.pricing_status_code = G_STATUS_NEW
4112: AND ldet.applied_flag = G_YES -- bug 6111048/6328486
4113: AND ldet.process_code IN
4112: AND ldet.applied_flag = G_YES -- bug 6111048/6328486
4113: AND ldet.process_code IN
4114: (G_STATUS_NEW, G_STATUS_UPDATED))
4115: --for bug#3224658
4116: OR EXISTS (SELECT 'Y' FROM qp_npreq_ldets_tmp
4117: WHERE modifier_level_code = 'ORDER'
4118: AND applied_flag = 'YES' -- bug 6628324
4119: AND pricing_status_code = G_STATUS_NEW
4120: AND process_code IN (G_STATUS_NEW, G_STATUS_UPDATED))
4121: OR EXISTS (SELECT 'Y' FROM oe_price_adjustments adj
4122: WHERE line.line_type_code = G_LINE_LEVEL
4123: AND line.line_id = adj.line_id
4124: AND adj.applied_flag = G_YES
4125: AND adj.list_line_id NOT IN (SELECT ldet.created_from_list_line_id FROM qp_npreq_ldets_tmp ldet
4126: WHERE ldet.line_index = line.line_index
4127: AND ldet.pricing_status_code = G_STATUS_NEW
4128: AND ldet.process_code IN (G_STATUS_NEW, G_STATUS_UPDATED)
4129: AND ldet.applied_flag = G_YES))
4130: OR EXISTS (SELECT 'Y' FROM oe_price_adjustments adj
4131: WHERE line.line_type_code = G_ORDER_LEVEL
4132: AND line.line_id = adj.header_id
4133: AND adj.applied_flag = G_YES
4134: AND adj.list_line_id NOT IN (SELECT ldet.created_from_list_line_id FROM qp_npreq_ldets_tmp ldet
4135: WHERE ldet.line_index = line.line_index
4136: AND ldet.pricing_status_code = G_STATUS_NEW
4137: AND ldet.process_code IN (G_STATUS_NEW, G_STATUS_UPDATED)
4138: AND ldet.applied_flag = G_YES))
4372:
4373: --begin SL_latest
4374: CURSOR l_net_mod_index_cur(p_list_line_id NUMBER) IS
4375: SELECT DISTINCT ldet.line_index
4376: FROM qp_npreq_ldets_tmp ldet
4377: WHERE ldet.created_from_list_line_id = p_list_line_id
4378: AND pricing_status_code IN (G_STATUS_NEW, G_STATUS_UPDATED, G_STATUS_UNCHANGED);
4379:
4380: l_line_bucket_amt NUMBER := 0;
6951: END IF;
6952: IF l_ldet_line_dtl_index_tbl.COUNT > 0
6953: THEN
6954: FORALL i IN l_ldet_line_dtl_index_tbl.FIRST..l_ldet_line_dtl_index_tbl.LAST
6955: UPDATE qp_npreq_ldets_tmp
6956: SET adjustment_amount = l_ldet_adj_amt_tbl(i),
6957: operand_value = l_ldet_operand_value_tbl(i),
6958: line_quantity = l_ldet_line_quantity_tbl(i),
6959: applied_flag = l_ldet_applied_flag_tbl(i),
6974: END IF;
6975: END IF; --l_ldet_line_dtl_index_tbl.count > 0
6976:
6977: FORALL i IN l_ldet_line_dtl_index_tbl.FIRST..l_ldet_line_dtl_index_tbl.LAST
6978: INSERT INTO qp_npreq_ldets_tmp
6979: (
6980: line_detail_index
6981: , line_index
6982: , line_detail_type_code
7209: /*
7210: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
7211: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_lines_tmp_N1,LINE_TYPE_CODE,2
7212: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_lines_tmp_N2,LINE_TYPE_CODE,1
7213: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
7214: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,2
7215: */
7216: CURSOR l_bucket_price_cur(p_line_index NUMBER) IS -- 2892848
7217: SELECT ldet.created_from_list_line_id
7210: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
7211: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_lines_tmp_N1,LINE_TYPE_CODE,2
7212: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_lines_tmp_N2,LINE_TYPE_CODE,1
7213: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
7214: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,2
7215: */
7216: CURSOR l_bucket_price_cur(p_line_index NUMBER) IS -- 2892848
7217: SELECT ldet.created_from_list_line_id
7218: , line.line_index line_ind
7246: , line.rounding_factor
7247: , G_LINE_LEVEL line_type
7248: , 'N' is_max_frt
7249: , ldet.net_amount_flag
7250: FROM qp_npreq_ldets_tmp ldet, qp_npreq_lines_tmp line
7251: --where line.line_index = p_line_index -- 2892848
7252: WHERE ldet.line_index = line.line_index -- 2892848
7253: --and ldet.line_index = line.line_index --2892848
7254: AND line.price_flag IN (G_YES, G_PHASE, G_CALCULATE_ONLY)
7305: , line.rounding_factor
7306: , G_ORDER_LEVEL line_type
7307: , 'N' is_max_frt
7308: , ldet.net_amount_flag
7309: FROM qp_npreq_ldets_tmp ldet, qp_npreq_lines_tmp line
7310: , qp_npreq_lines_tmp line1
7311: --where line.line_index = p_line_index -- 2892848
7312: --and ldet.line_index = line1.line_index -- 2892848
7313: WHERE ldet.line_index = line1.line_index -- 2892848
7339:
7340: CURSOR l_chk_backcal_adj_exist_cur(p_line_index NUMBER) IS
7341: SELECT ldet.line_detail_index
7342: , ldet.adjustment_amount
7343: FROM qp_npreq_ldets_tmp ldet
7344: WHERE line_index = p_line_index
7345: AND calculation_code = G_BACK_CALCULATE
7346: AND applied_flag = G_YES
7347: AND updated_flag = G_YES;
7379: l_amount_changed NUMBER := g_miss_num; --FND_API.G_MISS_NUM;
7380: l_adjustment_amount NUMBER := g_miss_num; --FND_API.G_MISS_NUM;
7381: l_operand_value NUMBER := g_miss_num; --FND_API.G_MISS_NUM;
7382: l_BACK_CALCULATE_START_TYPE VARCHAR2(30) := G_BACK_CALCULATION_STS_NONE; -- 2892848
7383: --l_prev_bucket qp_npreq_ldets_tmp.PRICING_GROUP_SEQUENCE%TYPE := g_miss_num;--FND_API.G_MISS_NUM;
7384: l_back_calc_dtl_index NUMBER := 0;
7385: l_back_calc_adj_amt NUMBER := 0;
7386:
7387: l_ldet_index_tbl QP_PREQ_GRP.NUMBER_TYPE;
7438: */
7439: --begin SL_latest
7440: CURSOR l_net_mod_index_cur(p_list_line_id NUMBER) IS
7441: SELECT DISTINCT ldet.line_index
7442: FROM qp_npreq_ldets_tmp ldet
7443: WHERE ldet.created_from_list_line_id = p_list_line_id
7444: AND pricing_status_code IN (G_STATUS_NEW, G_STATUS_UPDATED, G_STATUS_UNCHANGED);
7445:
7446: l_line_bucket_amt NUMBER := NULL;
8796: ||' count '|| l_ldet_tbl.COUNT);
8797: END IF;
8798:
8799: /*
8800: INDX,QP_PREQ_PUB.calculate_price.upd1,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
8801: */
8802: UPDATE qp_npreq_ldets_tmp SET
8803: adjustment_amount = l_ldet_tbl(x).adjustment_amount,
8804: applied_flag = l_ldet_tbl(x).applied_flag,
8798:
8799: /*
8800: INDX,QP_PREQ_PUB.calculate_price.upd1,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
8801: */
8802: UPDATE qp_npreq_ldets_tmp SET
8803: adjustment_amount = l_ldet_tbl(x).adjustment_amount,
8804: applied_flag = l_ldet_tbl(x).applied_flag,
8805: line_quantity = l_ldet_tbl(x).priced_quantity,
8806: --included for freight charge functionality
8997: AND l_back_calc_dtl_index <>
8998: l_back_calc_ret_rec.line_detail_index
8999: THEN
9000: --delete the existing back calculated adj
9001: UPDATE qp_npreq_ldets_tmp
9002: SET applied_flag = G_NO,
9003: calculation_code = NULL,
9004: pricing_status_code = G_STATUS_DELETED,
9005: pricing_status_text =
9008:
9009: END IF;
9010:
9011: /*
9012: INDX,QP_PREQ_PUB.calculate_price.upd2,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
9013: */
9014: UPDATE qp_npreq_ldets_tmp ldet
9015: SET ldet.operand_value =
9016: l_back_calc_ret_rec.operand_value
9010:
9011: /*
9012: INDX,QP_PREQ_PUB.calculate_price.upd2,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
9013: */
9014: UPDATE qp_npreq_ldets_tmp ldet
9015: SET ldet.operand_value =
9016: l_back_calc_ret_rec.operand_value
9017: , ldet.adjustment_amount =
9018: l_back_calc_ret_rec.adjustment_amount
9165: QP_PREQ_GRP.engine_debug('updating order lvl adj amt: '||
9166: l_ord_dtl_index_tbl.COUNT);
9167: END IF;
9168: FORALL i IN l_ord_dtl_index_tbl.FIRST..l_ord_dtl_index_tbl.LAST
9169: UPDATE qp_npreq_ldets_tmp
9170: SET adjustment_amount = l_ord_adj_amt_tbl(i)
9171: WHERE line_detail_index = l_ord_dtl_index_tbl(i);
9172: END IF;
9173:
9382: --mark the engine passed relationships of manual PBH as deleted
9383: --if the parent PBH line is deleted
9384: UPDATE qp_npreq_rltd_lines_tmp SET pricing_status_code = G_STATUS_DELETED
9385: WHERE line_detail_index IN (SELECT line_detail_index
9386: FROM qp_npreq_ldets_tmp ldet
9387: WHERE (ldet.process_code = G_STATUS_DELETED
9388: OR ldet.pricing_status_code = G_STATUS_DELETED))
9389: --fix for bug 2515762 automatic overrideable break
9390: -- and ldet.automatic_flag = G_NO)
9391: AND pricing_status_code = G_STATUS_NEW
9392: AND relationship_type_code = G_PBH_LINE;
9393:
9394: --mark the engine returned manual child PBH lines as deleted
9395: UPDATE qp_npreq_ldets_tmp ldet SET pricing_status_code = G_STATUS_DELETED
9396: WHERE ldet.line_detail_index IN
9397: (SELECT rltd.related_line_detail_index
9398: FROM qp_npreq_rltd_lines_tmp rltd
9399: WHERE rltd.relationship_type_code = G_PBH_LINE
9440: qualifier_value
9441: FROM
9442: qp_npreq_rltd_lines_tmp rltd,
9443: qp_npreq_lines_tmp line,
9444: qp_npreq_ldets_tmp ldet,
9445: qp_npreq_ldets_tmp ldet_pbh,
9446: qp_pricing_attributes qpa
9447: WHERE
9448: rltd.pricing_status_code = G_STATUS_NEW
9441: FROM
9442: qp_npreq_rltd_lines_tmp rltd,
9443: qp_npreq_lines_tmp line,
9444: qp_npreq_ldets_tmp ldet,
9445: qp_npreq_ldets_tmp ldet_pbh,
9446: qp_pricing_attributes qpa
9447: WHERE
9448: rltd.pricing_status_code = G_STATUS_NEW
9449: AND ldet.line_detail_index =
9518: nvl(ldet.line_quantity,
9519: nvl(line.priced_quantity, line.line_quantity))
9520: FROM
9521: qp_npreq_lines_tmp line,
9522: qp_npreq_ldets_tmp ldet,
9523: qp_npreq_ldets_tmp ldet_pbh,
9524: qp_pricing_attributes qpa
9525: WHERE
9526: ldet.line_detail_index = rltd.related_line_detail_index
9519: nvl(line.priced_quantity, line.line_quantity))
9520: FROM
9521: qp_npreq_lines_tmp line,
9522: qp_npreq_ldets_tmp ldet,
9523: qp_npreq_ldets_tmp ldet_pbh,
9524: qp_pricing_attributes qpa
9525: WHERE
9526: ldet.line_detail_index = rltd.related_line_detail_index
9527: AND ldet_pbh.line_detail_index = rltd.line_detail_index
9531: AND ldet.pricing_status_code = G_STATUS_UNCHANGED
9532: AND rltd.relationship_type_code = G_PBH_LINE
9533: AND rltd.pricing_status_code = G_STATUS_NEW)
9534: WHERE rltd.line_detail_index IN (SELECT ldet.line_detail_index
9535: FROM qp_npreq_ldets_tmp ldet
9536: WHERE ldet.process_code = G_STATUS_NEW
9537: AND ldet.pricing_status_code = G_STATUS_UNCHANGED
9538: AND ldet.created_from_list_line_type = G_PRICE_BREAK_TYPE)
9539: AND rltd.relationship_type_code = G_PBH_LINE
9570: , X_RETURN_STATUS OUT NOCOPY VARCHAR2
9571: , X_RETURN_STATUS_TEXT OUT NOCOPY VARCHAR2) IS
9572:
9573: /*
9574: INDX,QP_PREQ_PUB.process_adjustments.l_duplicate_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
9575: INDX,QP_PREQ_PUB.process_adjustments.l_duplicate_cur,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
9576: */
9577: CURSOR l_duplicate_cur IS
9578: SELECT /*+ index (ldeta qp_preq_ldets_tmp_n5) */ -- 9362867
9571: , X_RETURN_STATUS_TEXT OUT NOCOPY VARCHAR2) IS
9572:
9573: /*
9574: INDX,QP_PREQ_PUB.process_adjustments.l_duplicate_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
9575: INDX,QP_PREQ_PUB.process_adjustments.l_duplicate_cur,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
9576: */
9577: CURSOR l_duplicate_cur IS
9578: SELECT /*+ index (ldeta qp_preq_ldets_tmp_n5) */ -- 9362867
9579: ldeta.line_detail_index
9583: , ldeta.process_code
9584: , ldeta.pricing_status_text
9585: , ldeta.applied_flag
9586: , ldeta.updated_flag
9587: FROM qp_npreq_ldets_tmp ldeta
9588: WHERE --ldeta.pricing_status_code = 'X'
9589: ldeta.process_code = G_STATUS_NEW
9590: AND ldeta.created_from_list_line_id IN
9591: (SELECT /*+ index (ldetb qp_preq_ldets_tmp_n5) */ -- 9362867
9589: ldeta.process_code = G_STATUS_NEW
9590: AND ldeta.created_from_list_line_id IN
9591: (SELECT /*+ index (ldetb qp_preq_ldets_tmp_n5) */ -- 9362867
9592: ldetb.created_from_list_line_id
9593: FROM qp_npreq_ldets_tmp ldetb
9594: WHERE ldetb.created_from_list_line_id =
9595: ldeta.created_from_list_line_id
9596: AND ldetb.line_index = ldeta.line_index
9597: AND ldetb.process_code = G_STATUS_NEW
9617: SELECT line_index
9618: , created_from_list_line_id
9619: , pricing_status_code, applied_flag
9620: , updated_flag, operand_value
9621: FROM qp_npreq_ldets_tmp
9622: WHERE PRICING_STATUS_CODE = G_STATUS_UNCHANGED;
9623:
9624: lrec lcur%ROWTYPE;
9625:
9623:
9624: lrec lcur%ROWTYPE;
9625:
9626: /*
9627: INDX,QP_PREQ_PUB.process_adjustments.lcur1,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
9628: INDX,QP_PREQ_PUB.process_adjustments.lcur1,qp_npreq_ldets_tmp_N3,PRICING_STATUS_CODE,4
9629: */
9630: CURSOR lcur1 IS
9631: SELECT created_from_list_line_id
9624: lrec lcur%ROWTYPE;
9625:
9626: /*
9627: INDX,QP_PREQ_PUB.process_adjustments.lcur1,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
9628: INDX,QP_PREQ_PUB.process_adjustments.lcur1,qp_npreq_ldets_tmp_N3,PRICING_STATUS_CODE,4
9629: */
9630: CURSOR lcur1 IS
9631: SELECT created_from_list_line_id
9632: , line_detail_index
9635: , applied_flag
9636: , updated_flag
9637: , pricing_status_code
9638: , process_code
9639: FROM qp_npreq_ldets_tmp
9640: ORDER BY line_index;
9641:
9642: lrec1 lcur1%ROWTYPE;
9643:
9670:
9671: ----------------------------------------------------------------------------
9672: --The following code will process the information returned by the
9673: --pricing engine in the temporary tables, compare the adjustments passes
9674: --by the calling application and mark the temporary table qp_npreq_ldets_tmp
9675: --field process_code with a value G_STATUS_NEW
9676: ----------------------------------------------------------------------------
9677: IF l_debug = FND_API.G_TRUE THEN
9678: QP_PREQ_GRP.ENGINE_DEBUG('START PROCESS ADJUSTMENTS '|| p_pricing_event);
9680: END IF;
9681: --Step 0: Update all user passed adjustments to process_code 'DELETED'
9682: --to start with
9683:
9684: UPDATE qp_npreq_ldets_tmp ldet SET ldet.process_code = G_STATUS_DELETED
9685: WHERE ldet.pricing_status_code = G_STATUS_UNCHANGED;
9686:
9687: --First Step: Retain the adjustments input by user
9688: --which are in the phase which do not belong to the
9691:
9692: /*
9693: INDX,QP_PREQ_PUB.process_adjustments.upd1,QP_EVENT_PHASES_U1,PRICING_EVENT_CODE,1
9694: */
9695: UPDATE qp_npreq_ldets_tmp ldet SET ldet.process_code = G_STATUS_NEW
9696: WHERE ldet.pricing_status_code = G_STATUS_UNCHANGED AND
9697: ldet.applied_flag = G_YES AND
9698: ldet.pricing_phase_id NOT IN (SELECT ev.pricing_phase_id
9699: FROM qp_event_phases ev, qp_pricing_phases ph
9719: upd2 indxno index used
9720: */
9721: --bug 2264566 update the child lines of manual PBH which get
9722: --passed with applied_flag null and updated_flag = 'Y'
9723: UPDATE qp_npreq_ldets_tmp ldet SET ldet.process_code = G_STATUS_NEW
9724: WHERE ldet.pricing_status_code = G_STATUS_UNCHANGED AND
9725: --fix for bug 2515762 automatic overrideable break
9726: ldet.updated_flag = G_YES;
9727: -- ((ldet.updated_flag = G_YES and ldet.applied_flag = G_YES)
9736: QP_PREQ_GRP.ENGINE_DEBUG('PROCESS ADJUSTMENTS: Step1.5');
9737: END IF;
9738: --summary line adjustments with price flag 'N' need to be considered for calc
9739: --reqt from contracts for performance
9740: UPDATE qp_npreq_ldets_tmp ldet SET ldet.process_code = G_STATUS_NEW
9741: WHERE ldet.line_index IN
9742: (SELECT line.line_index FROM qp_npreq_lines_tmp line
9743: WHERE line.line_type_code = G_ORDER_LEVEL
9744: AND line.price_flag = G_NO);
9753:
9754: /*
9755: upd3 indxno index used
9756: */
9757: UPDATE qp_npreq_ldets_tmp ldet SET ldet.process_code = G_STATUS_DELETED
9758: WHERE ldet.process_code = G_STATUS_NEW
9759: AND ldet.pricing_status_code NOT IN
9760: (G_STATUS_NEW, G_STATUS_UNCHANGED); -- and ldet.applied_flag = 'Y';
9761:
9876: i := l_dup_adj_tbl.FIRST;
9877: WHILE i IS NOT NULL LOOP
9878:
9879: /*
9880: INDX,QP_PREQ_PUB.process_adjustments.upd4,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
9881: */
9882: UPDATE qp_npreq_ldets_tmp SET
9883: process_code = l_dup_adj_tbl(i).process_code
9884: , pricing_status_code = l_dup_adj_tbl(i).pricing_status_code
9878:
9879: /*
9880: INDX,QP_PREQ_PUB.process_adjustments.upd4,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
9881: */
9882: UPDATE qp_npreq_ldets_tmp SET
9883: process_code = l_dup_adj_tbl(i).process_code
9884: , pricing_status_code = l_dup_adj_tbl(i).pricing_status_code
9885: , pricing_status_text = l_dup_adj_tbl(i).pricing_status_text
9886: WHERE line_detail_index = l_dup_adj_tbl(i).line_detail_index;
10027: CURSOR l_child_break_cur IS
10028: SELECT /*+ ORDERED index(ldet QP_PREQ_LDETS_TMP_U1) */ldet.process_code,
10029: rltd.related_line_detail_index
10030: FROM qp_npreq_rltd_lines_tmp rltd,
10031: -- qp_npreq_ldets_tmp ldet1,
10032: qp_npreq_ldets_tmp ldet
10033: WHERE rltd.relationship_type_code = G_PBH_LINE
10034: AND rltd.pricing_status_code = G_STATUS_NEW
10035: AND ldet.line_index = rltd.line_index
10028: SELECT /*+ ORDERED index(ldet QP_PREQ_LDETS_TMP_U1) */ldet.process_code,
10029: rltd.related_line_detail_index
10030: FROM qp_npreq_rltd_lines_tmp rltd,
10031: -- qp_npreq_ldets_tmp ldet1,
10032: qp_npreq_ldets_tmp ldet
10033: WHERE rltd.relationship_type_code = G_PBH_LINE
10034: AND rltd.pricing_status_code = G_STATUS_NEW
10035: AND ldet.line_index = rltd.line_index
10036: AND ldet.line_detail_index = rltd.line_detail_index
10082: IF l_debug = FND_API.G_TRUE THEN
10083: QP_PREQ_GRP.engine_debug('before update');
10084: END IF;
10085: FORALL j IN l_line_dtl_index_tbl.FIRST..l_line_dtl_index_tbl.LAST
10086: UPDATE qp_npreq_ldets_tmp
10087: SET process_code = l_child_sts_code_tbl(j)
10088: WHERE line_detail_index = l_line_dtl_index_tbl(j);
10089: IF l_debug = FND_API.G_TRUE THEN
10090: QP_PREQ_GRP.engine_debug('after update');
10147: ldet.group_amount,
10148: line.parent_price
10149: FROM qp_npreq_lines_tmp line,
10150: qp_npreq_rltd_lines_tmp rltd,
10151: qp_npreq_ldets_tmp ldet,
10152: qp_pricing_attributes qpa,
10153: qp_npreq_line_attrs_tmp attr
10154: WHERE line.pricing_status_code IN (G_STATUS_UNCHANGED, G_STATUS_UPDATED)
10155: AND line.price_flag = G_CALCULATE_ONLY
10442: || l_line_quantity_tbl.FIRST ||' last '
10443: || l_line_quantity_tbl.LAST);
10444: END IF;
10445: FORALL i IN l_line_quantity_tbl.FIRST..l_line_quantity_tbl.LAST
10446: UPDATE qp_npreq_ldets_tmp
10447: SET line_quantity = l_line_quantity_tbl(i)
10448: , applied_flag = l_applied_flag_tbl(i)
10449: , adjustment_amount = l_adj_amt_tbl(i)
10450: WHERE line_detail_index = l_line_dtl_index_tbl(i);
10470: IF l_debug = FND_API.G_TRUE THEN
10471: QP_PREQ_GRP.engine_debug('update PBH ');
10472: END IF;
10473: FORALL i IN x_line_index_tbl.FIRST..x_line_index_tbl.LAST
10474: UPDATE qp_npreq_ldets_tmp
10475: SET line_quantity = l_total_qty_tbl(i)
10476: , applied_flag = G_YES
10477: , adjustment_amount = x_list_price_tbl(i)
10478: WHERE line_index = x_line_index_tbl(i)
10481: (G_PRICE_LIST_HEADER, G_AGR_LIST_HEADER)
10482: AND x_pricing_sts_code_tbl(i) = G_STATUS_UPDATED;
10483: END IF; --x_line_index_tbl.COUNT
10484:
10485: UPDATE qp_npreq_ldets_tmp
10486: SET applied_flag = G_NO
10487: WHERE created_from_list_line_type = G_PRICE_BREAK_TYPE
10488: AND created_from_list_type_code IN
10489: (G_PRICE_LIST_HEADER, G_AGR_LIST_HEADER)
10598: , line.rounding_factor
10599: , line.uom_quantity service_duration
10600: , line.processing_order
10601: , line.parent_price
10602: FROM qp_npreq_lines_tmp line, qp_npreq_ldets_tmp ldet
10603: WHERE line.usage_pricing_type = QP_PREQ_GRP.G_BILLING_TYPE
10604: AND ldet.line_index = line.line_index
10605: AND line.price_flag = G_CALCULATE_ONLY
10606: AND nvl(processing_order, 1) = p_processing_order
10711: IF l_debug = FND_API.G_TRUE THEN
10712: QP_PREQ_GRP.engine_debug('Begin Usage Pricing LOOP1');
10713: END IF;
10714: FORALL i IN l_line_dtl_index_tbl.FIRST..l_line_dtl_index_tbl.LAST
10715: UPDATE qp_npreq_ldets_tmp
10716: SET adjustment_amount = l_adj_amt_tbl(i)
10717: WHERE line_detail_index = l_line_dtl_index_tbl(i);
10718: END IF; --l_line_dtl_index_tbl.COUNT
10719:
10824: IF l_debug = FND_API.G_TRUE THEN
10825: QP_PREQ_GRP.engine_debug('Begin Usage Pricing LOOP2');
10826: END IF;
10827: FORALL i IN l_line_dtl_index_tbl.FIRST..l_line_dtl_index_tbl.LAST
10828: UPDATE qp_npreq_ldets_tmp
10829: SET adjustment_amount = l_adj_amt_tbl(i)
10830: WHERE line_detail_index = l_line_dtl_index_tbl(i);
10831: END IF; --l_line_dtl_index_tbl.COUNT
10832:
11015: oldprg.updated_flag
11016: FROM qp_npreq_lines_tmp buyline
11017: , qp_event_phases ev
11018: , qp_pricing_phases ph
11019: , qp_npreq_ldets_tmp oldprg
11020: , qp_npreq_rltd_lines_tmp oldrltd
11021: , qp_npreq_ldets_tmp oldfgdis
11022: , qp_npreq_lines_tmp oldfreeline
11023: --where G_REQUEST_TYPE_CODE <> 'ONT'
11017: , qp_event_phases ev
11018: , qp_pricing_phases ph
11019: , qp_npreq_ldets_tmp oldprg
11020: , qp_npreq_rltd_lines_tmp oldrltd
11021: , qp_npreq_ldets_tmp oldfgdis
11022: , qp_npreq_lines_tmp oldfreeline
11023: --where G_REQUEST_TYPE_CODE <> 'ONT'
11024: --bug 3085453 handle pricing availability UI
11025: -- they pass reqtype ONT and insert adj into ldets
11167: INTO l_fgdline_exist
11168: FROM dual
11169: WHERE EXISTS (SELECT 1
11170: FROM qp_npreq_rltd_lines_tmp oldrltd,
11171: qp_npreq_ldets_tmp oldprg,
11172: qp_event_phases ev
11173: WHERE oldrltd.relationship_type_code = G_GENERATED_LINE
11174: AND oldrltd.line_detail_index = oldprg.line_detail_index
11175: AND oldrltd.line_index = oldprg.line_index
11313: FOR i IN l_mark_prg_delete_index.FIRST..l_mark_prg_delete_index.LAST
11314: LOOP
11315: BEGIN
11316: SELECT created_from_list_line_id INTO l_prg_list_line_id
11317: FROM qp_npreq_ldets_tmp
11318: WHERE line_detail_index = l_mark_prg_delete_index(i);
11319: QP_PREQ_GRP.engine_debug('Mark delete prg '|| l_mark_prg_delete_index(i)
11320: ||' list_line_id '|| l_prg_list_line_id);
11321: EXCEPTION
11325: END LOOP;
11326: END IF; --l_debug
11327: IF QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = 'N' THEN
11328: FORALL i IN l_mark_prg_delete_index.FIRST..l_mark_prg_delete_index.LAST
11329: UPDATE qp_npreq_ldets_tmp SET pricing_status_code = G_STATUS_DELETED
11330: WHERE line_detail_index = l_mark_prg_delete_index(i);
11331: END IF;
11332: END IF; --l_mark_prg_delete_index.COUNT
11333:
11337: FOR i IN l_mark_fgdis_delete_index.FIRST..l_mark_fgdis_delete_index.LAST
11338: LOOP
11339: BEGIN
11340: SELECT created_from_list_line_id INTO l_prg_list_line_id
11341: FROM qp_npreq_ldets_tmp
11342: WHERE line_detail_index = l_mark_fgdis_delete_index(i);
11343: QP_PREQ_GRP.engine_debug('Mark delete fgdis '|| l_mark_fgdis_delete_index
11344: (i)
11345: ||' list_line_id '|| l_prg_list_line_id);
11349: END;
11350: END LOOP;
11351: END IF; --l_debug
11352: FORALL i IN l_mark_fgdis_delete_index.FIRST..l_mark_fgdis_delete_index.LAST
11353: UPDATE qp_npreq_ldets_tmp SET pricing_status_code = G_STATUS_DELETED
11354: WHERE line_detail_index = l_mark_fgdis_delete_index(i);
11355: END IF; --l_mark_fgdis_delete_index.count
11356: IF l_debug = FND_API.G_TRUE THEN
11357: QP_PREQ_GRP.engine_debug('End Update_prg_pricing_status');
11481: oldfreeline.SERVICE_DURATION old_SERVICE_DURATION, -- service project
11482: oldfreeline.SERVICE_PERIOD old_SERVICE_PERIOD -- service project
11483: FROM qp_npreq_lines_tmp oldfreeline
11484: , qp_npreq_rltd_lines_tmp newrltd
11485: , qp_npreq_ldets_tmp newfgdis
11486: , qp_npreq_lines_tmp newfgline
11487: , qp_npreq_line_attrs_tmp oldfreeitem
11488: , qp_npreq_line_attrs_tmp newfgitem
11489: --where G_REQUEST_TYPE_CODE = 'ONT'
11605: oldfreeline.SERVICE_DURATION old_SERVICE_DURATION, -- service project
11606: oldfreeline.SERVICE_PERIOD old_SERVICE_PERIOD -- service project
11607: FROM qp_npreq_lines_tmp oldfreeline
11608: , qp_npreq_rltd_lines_tmp newrltd
11609: , qp_npreq_ldets_tmp newfgdis
11610: , qp_npreq_lines_tmp newfgline
11611: , qp_npreq_line_attrs_tmp newfgitem
11612: , qp_npreq_line_attrs_tmp oldfreeitem
11613: --WHERE G_REQUEST_TYPE_CODE <> 'ONT'
11635:
11636: CURSOR l_updated_prg_fg_cur IS
11637: SELECT /*+ INDEX(prg OE_PRICE_ADJUSTMENTS_N2) */ rltd.related_line_index
11638: FROM qp_npreq_lines_tmp buyline, oe_price_adjustments prg,
11639: qp_npreq_ldets_tmp ldet, qp_npreq_rltd_lines_tmp rltd
11640: --WHERE G_REQUEST_TYPE_CODE = 'ONT'
11641: --bug 3085453 handle pricing availability UI
11642: -- they pass reqtype ONT and insert adj into ldets
11643: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = G_YES
11654: AND rltd.pricing_status_code = G_STATUS_NEW
11655: UNION
11656: SELECT /*+ INDEX(prg OE_PRICE_ADJUSTMENTS_N1) */ rltd.related_line_index
11657: FROM qp_npreq_lines_tmp buyline, oe_price_adjustments prg,
11658: qp_npreq_ldets_tmp ldet, qp_npreq_rltd_lines_tmp rltd
11659: --WHERE G_REQUEST_TYPE_CODE = 'ONT'
11660: --bug 3085453 handle pricing availability UI
11661: -- they pass reqtype ONT and insert adj into ldets
11662: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = G_YES
11674: AND rltd.pricing_status_code = G_STATUS_NEW
11675: UNION
11676: -- hint added for 5575718
11677: SELECT /*+ ORDERED */ rltd.related_line_index
11678: FROM qp_npreq_lines_tmp buyline, qp_npreq_ldets_tmp prg,
11679: qp_npreq_ldets_tmp ldet, qp_npreq_rltd_lines_tmp rltd
11680: --WHERE G_REQUEST_TYPE_CODE <> 'ONT'
11681: --bug 3085453 handle pricing availability UI
11682: -- they pass reqtype ONT and insert adj into ldets
11675: UNION
11676: -- hint added for 5575718
11677: SELECT /*+ ORDERED */ rltd.related_line_index
11678: FROM qp_npreq_lines_tmp buyline, qp_npreq_ldets_tmp prg,
11679: qp_npreq_ldets_tmp ldet, qp_npreq_rltd_lines_tmp rltd
11680: --WHERE G_REQUEST_TYPE_CODE <> 'ONT'
11681: --bug 3085453 handle pricing availability UI
11682: -- they pass reqtype ONT and insert adj into ldets
11683: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG <> G_YES
11728: G_prg_unch_process_sts_tbl.DELETE;
11729:
11730: BEGIN
11731: SELECT 'Y' INTO l_Process_PRG
11732: FROM qp_npreq_ldets_tmp
11733: WHERE created_from_list_line_type = G_PROMO_GOODS_DISCOUNT
11734: AND pricing_status_code = G_STATUS_NEW
11735: AND applied_flag = 'Y'
11736: AND ROWNUM = 1;
11786: AND adj.line_id IS NULL
11787: AND adj.list_line_type_code = G_PROMO_GOODS_DISCOUNT
11788: AND adj.updated_flag = G_YES
11789: UNION
11790: SELECT 'Y' FROM qp_npreq_ldets_tmp adj
11791: WHERE adj.created_from_list_line_type = G_PROMO_GOODS_DISCOUNT
11792: AND adj.pricing_status_code = G_STATUS_UNCHANGED
11793: AND adj.updated_flag = G_YES);
11794: END IF; --l_PROCESS_PRG
11835: ||'prg listlineid '|| cl.list_line_id ||' updatedflag '|| cl.updated_flag
11836: ||' buylineid '|| cl.line_id ||' prgphaseid '|| cl.pricing_phase_id);
11837: FOR fg IN
11838: (SELECT rltd.related_line_index, ldet.created_from_list_line_id
11839: FROM qp_npreq_ldets_tmp ldet, qp_npreq_rltd_lines_tmp rltd
11840: WHERE ldet.line_index = cl.line_index
11841: AND ldet.pricing_status_code = G_STATUS_NEW
11842: AND ldet.applied_flag = G_YES
11843: AND ldet.created_from_list_line_id = cl.list_line_id
11916: THEN
11917: FOR cl IN (SELECT newprg.line_index line_ind
11918: , newprg.created_from_list_line_id
11919: FROM qp_npreq_lines_tmp oldfgline
11920: , qp_npreq_ldets_tmp newprg
11921: WHERE --ldet.line_index = oldfgline.line_index
11922: instr(oldfgline.process_status
11923: , G_BUYLINE || newprg.line_index || G_PROMO_GOODS_DISCOUNT) > 0
11924: AND newprg.applied_flag = G_YES
11939: --PRG modifiers against its buy line
11940: UPDATE qp_npreq_lines_tmp oldfgline
11941: SET oldfgline.process_status = G_STATUS_DELETED
11942: WHERE NOT EXISTS (SELECT newprg.line_index
11943: FROM qp_npreq_ldets_tmp newprg
11944: --, qp_npreq_line_attrs_tmp newfgitem <-- commented out, sql repos
11945: WHERE --G_REQUEST_TYPE_CODE = 'ONT'
11946: newprg.pricing_status_code = G_STATUS_NEW
11947: AND newprg.applied_flag = G_YES
12274: CURSOR l_buyline_price_flag_cur IS
12275: SELECT /*+ ORDERED USE_NL(buyline prg dis)*/
12276: dis.created_from_list_line_id, prg.line_detail_index, buyline.price_flag, 'Y' is_ldet, dis.line_index
12277: FROM qp_npreq_rltd_lines_tmp rltd, qp_npreq_lines_tmp buyline
12278: , qp_npreq_ldets_tmp prg, qp_npreq_ldets_tmp dis
12279: WHERE rltd.pricing_status_code = G_STATUS_NEW
12280: AND rltd.relationship_type_code = G_GENERATED_LINE
12281: AND buyline.line_index = rltd.line_index
12282: AND prg.line_detail_index = rltd.line_detail_index
12304: IF l_debug = FND_API.G_TRUE THEN
12305: QP_PREQ_GRP.engine_debug('loop linedtl '|| c1.line_detail_index ||' rltddtl '
12306: || c1.related_line_detail_index ||' lineind '|| c1.line_index);
12307: END IF;
12308: FOR c2 IN (SELECT line_index, line_detail_index FROM qp_npreq_ldets_tmp
12309: WHERE line_detail_index = c1.line_detail_index
12310: AND pricing_status_code = G_STATUS_NEW
12311: AND created_from_list_line_type IN
12312: (G_OTHER_ITEM_DISCOUNT, G_PROMO_GOODS_DISCOUNT))
12351: OPEN l_buyline_price_flag_cur;
12352: FETCH l_buyline_price_flag_cur BULK COLLECT INTO
12353: G_buyline_list_line_id, G_buyline_price_flag;
12354: CLOSE l_buyline_price_flag_cur;
12355: UPDATE qp_npreq_ldets_tmp freegood_dis set buy_line_price_flag =
12356: (select buyline.price_flag
12357: from qp_npreq_rltd_lines_tmp rl, qp_npreq_lines_tmp buyline
12358: where rl.pricing_status_code = G_STATUS_NEW
12359: and rl.relationship_type_code = G_GENERATED_LINE
12443: created_from_list_line_id
12444: , line_quantity
12445: , line_detail_index
12446: , pricing_phase_id
12447: FROM qp_npreq_ldets_tmp ldets
12448: WHERE created_from_list_line_type = G_COUPON_ISSUE
12449: AND applied_flag = G_YES
12450: /*
12451: --you don't need to match the phase as this will be called after cleanup
12520: , ldet.accrual_flag
12521: , qpl.accrual_conversion_rate
12522: , qpl.estim_accrual_rate
12523: -- , qpl.benefit_qty
12524: FROM qp_npreq_ldets_tmp ldet, qp_npreq_line_attrs_tmp lattr, qp_list_lines qpl
12525: WHERE ldet.pricing_phase_id > 1
12526: AND ldet.pricing_status_code = G_STATUS_NEW
12527: AND ldet.line_index > -1
12528: AND ldet.created_from_list_line_type IN ('DIS', 'SUR', 'FREIGHT_CHARGE',
12613: IF l_debug = FND_API.G_TRUE THEN
12614: QP_PREQ_GRP.engine_debug('Updating recurring qty '|| l_recur_dtl_index_tbl.COUNT);
12615: END IF;
12616: FORALL i IN l_recur_dtl_index_tbl.FIRST .. l_recur_dtl_index_tbl.LAST
12617: UPDATE qp_npreq_ldets_tmp recur SET operand_value = -- shu fix 2702384
12618: l_recur_tot_benefit_qty_tbl(i)
12619: , benefit_qty = l_recur_benefit_qty_tbl(i) --Bug 2804053
12620: WHERE recur.line_detail_index = l_recur_dtl_index_tbl(i);
12621: END IF; --l_recur_dtl_index_tbl.COUNT
12914: FROM qp_npreq_lines_tmp;
12915:
12916: /*
12917: INDX,QP_PREQ_GRP.populate_output.l_ldets_cur,QP_LIST_LINES_PK,LIST_LINE_ID,1
12918: INDX,QP_PREQ_GRP.populate_output.l_ldets_cur,qp_npreq_ldets_tmp_N4,PRICING_STATUS_CODE,1
12919: INDX,QP_PREQ_GRP.populate_output.l_ldets_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
12920: */
12921: CURSOR l_ldets_cur(p_line_index NUMBER) IS
12922: SELECT /*+ ORDERED USE_NL(A B C) l_ldets_cur */
12977: b.EXPIRATION_PERIOD_UOM,
12978: b.COMMENTS,
12979: a.ORDER_QTY_OPERAND,
12980: a.ORDER_QTY_ADJ_AMT
12981: FROM qp_npreq_ldets_tmp a,
12982: QP_LIST_LINES b
12983: WHERE a.line_index = p_line_index
12984: AND a.CREATED_FROM_LIST_LINE_ID = b.LIST_LINE_ID
12985: AND a.PRICING_STATUS_CODE = G_STATUS_NEW;
13157: i PLS_INTEGER;
13158: j PLS_INTEGER;
13159:
13160: /*
13161: INDX,QP_PREQ_PUB.price_request.lcur1,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
13162: INDX,QP_PREQ_PUB.price_request.lcur1,qp_npreq_ldets_tmp_N3,PRICING_STATUS_CODE,4
13163: */
13164: CURSOR lcur1 IS
13165: SELECT ldet.created_from_list_line_id
13158: j PLS_INTEGER;
13159:
13160: /*
13161: INDX,QP_PREQ_PUB.price_request.lcur1,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
13162: INDX,QP_PREQ_PUB.price_request.lcur1,qp_npreq_ldets_tmp_N3,PRICING_STATUS_CODE,4
13163: */
13164: CURSOR lcur1 IS
13165: SELECT ldet.created_from_list_line_id
13166: , line.line_index line_ind
13177: , ldet.updated_flag
13178: , ldet.calculation_code
13179: , line.qualifiers_exist_flag
13180: , ldet.pricing_group_sequence bucket
13181: FROM qp_npreq_ldets_tmp ldet, qp_npreq_lines_tmp line
13182: WHERE line.line_index = ldet.line_index
13183: ORDER BY line_ind;
13184:
13185: --3169430
13999: --this needs to be done for OC/ASO as they pass in adj as G_STATUS_UNCHANGED
14000: /*
14001: indxno index used
14002: */
14003: UPDATE qp_npreq_ldets_tmp
14004: SET pricing_status_code = G_STATUS_NEW
14005: WHERE process_code = G_STATUS_NEW
14006: --changes for bug 2264566
14007: --changed to populate w/updated_flag = Y for child lines of manualPBH
14307: END IF;
14308: /*
14309: indxno index used Ravi
14310: */
14311: UPDATE qp_npreq_ldets_tmp
14312: SET pricing_status_code = G_STATUS_NEW
14313: WHERE process_code = G_STATUS_NEW
14314: --changes for bug 2264566
14315: --changed to populate w/updated_flag = Y for child lines of manualPBH