[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;
2765: ldet.pricing_group_sequence,
2766: nvl(ldet.line_quantity,
2767: nvl(line.priced_quantity, line.line_quantity))
2768: FROM
2769: qp_npreq_ldets_tmp ldet,
2770: qp_npreq_ldets_tmp ldet_pbh,
2771: qp_npreq_lines_tmp line,
2772: qp_pricing_attributes qpa
2773: WHERE
2766: nvl(ldet.line_quantity,
2767: nvl(line.priced_quantity, line.line_quantity))
2768: FROM
2769: qp_npreq_ldets_tmp ldet,
2770: qp_npreq_ldets_tmp ldet_pbh,
2771: qp_npreq_lines_tmp line,
2772: qp_pricing_attributes qpa
2773: WHERE
2774: ldet.line_detail_index = rltd.related_line_detail_index
2779: AND ldet.updated_flag = G_YES
2780: AND rltd.relationship_type_code = G_PBH_LINE
2781: AND rltd.pricing_status_code = G_STATUS_NEW)
2782: WHERE rltd.line_detail_index IN (SELECT ldet.line_detail_index
2783: FROM qp_npreq_ldets_tmp ldet
2784: WHERE ldet.pricing_status_code = G_STATUS_UNCHANGED
2785: AND ldet.applied_flag = G_YES
2786: AND ldet.updated_flag = G_YES
2787: AND ldet.created_from_list_line_type = G_PRICE_BREAK_TYPE)
2793: || SQL%ROWCOUNT);
2794: QP_PREQ_GRP.engine_debug('Updating the child detail type code');
2795: END IF;
2796:
2797: UPDATE qp_npreq_ldets_tmp
2798: SET line_detail_type_code = QP_PREQ_PUB.G_CHILD_DETAIL_TYPE
2799: WHERE line_detail_index IN (SELECT related_line_detail_index
2800: FROM qp_npreq_rltd_lines_tmp
2801: WHERE relationship_type_code = G_PBH_LINE
2802: AND pricing_status_code = G_STATUS_NEW);
2803:
2804: IF (l_debug = FND_API.G_TRUE) THEN
2805: FOR i IN (SELECT line_detail_index
2806: FROM qp_npreq_ldets_tmp
2807: WHERE line_detail_type_code = QP_PREQ_PUB.G_CHILD_DETAIL_TYPE)
2808: LOOP
2809: QP_PREQ_GRP.engine_debug(' PBH Child Line Detail Index : ' || i.line_detail_index);
2810: END LOOP;
3104: decode(ldet.calculation_code, 'BACK_CALCULATE', 10000,
3105: decode(ldet.created_from_list_line_type, 'DIS',
3106: decode(ldet.applied_flag, G_YES, - 1000, - 100),
3107: decode(ldet.applied_flag, G_YES, 100, 1000)))) precedence
3108: FROM qp_npreq_ldets_tmp ldet, qp_npreq_lines_tmp line
3109: WHERE line.line_index = p_line_index
3110: AND line.line_type_code = G_LINE_LEVEL -- sql repos
3111: AND line.price_flag IN (G_YES, G_PHASE)
3112: AND ldet.line_index = line.line_index
3596: --changed this for bug 2776800 to populate order_uom_selling_price
3597: --this means there are no adjustments and calculation has not taken place
3598: or line.order_uom_selling_price IS NULL )
3599: and not exists (select ldet.line_index
3600: from qp_npreq_ldets_tmp ldet
3601: where (ldet.line_index = line.line_index
3602: or ldet.modifier_level_code = G_ORDER_LEVEL)
3603: and nvl(ldet.created_from_list_type_code,'NULL') not in
3604: (G_PRICE_LIST_HEADER,G_AGR_LIST_HEADER)
4007: --added for bug 2926554
4008: IF l_ldet_dtl_index.COUNT > 0
4009: THEN
4010: FORALL i IN l_ldet_dtl_index.first..l_ldet_dtl_index.last
4011: UPDATE qp_npreq_ldets_tmp ldet
4012: SET ldet.operand_value = l_ldet_operand_value(i)
4013: , ldet.adjustment_amount = l_ldet_adjamt(i)
4014: , ldet.applied_flag = l_ldet_applied_flag(i)
4015: , ldet.updated_flag = l_ldet_updated_flag(i)
4086: AND (line.price_flag IN (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
4087: OR nvl(line.processed_code, '0') = QP_PREQ_PUB.G_BY_ENGINE)
4088: AND (G_CALCULATE_FLAG = G_CALCULATE_ONLY
4089:
4090: OR EXISTS (SELECT 'Y' FROM qp_npreq_ldets_tmp ldet
4091: WHERE ldet.line_index = line.line_index
4092: AND ldet.pricing_status_code = G_STATUS_NEW
4093: AND ldet.applied_flag = G_YES -- bug 6111048/6328486
4094: AND ldet.process_code IN
4093: AND ldet.applied_flag = G_YES -- bug 6111048/6328486
4094: AND ldet.process_code IN
4095: (G_STATUS_NEW, G_STATUS_UPDATED))
4096: --for bug#3224658
4097: OR EXISTS (SELECT 'Y' FROM qp_npreq_ldets_tmp
4098: WHERE modifier_level_code = 'ORDER'
4099: AND applied_flag = 'YES' -- bug 6628324
4100: AND pricing_status_code = G_STATUS_NEW
4101: AND process_code IN (G_STATUS_NEW, G_STATUS_UPDATED))
4102: OR EXISTS (SELECT 'Y' FROM oe_price_adjustments adj
4103: WHERE line.line_type_code = G_LINE_LEVEL
4104: AND line.line_id = adj.line_id
4105: AND adj.applied_flag = G_YES
4106: AND adj.list_line_id NOT IN (SELECT ldet.created_from_list_line_id FROM qp_npreq_ldets_tmp ldet
4107: WHERE ldet.line_index = line.line_index
4108: AND ldet.pricing_status_code = G_STATUS_NEW
4109: AND ldet.process_code IN (G_STATUS_NEW, G_STATUS_UPDATED)
4110: AND ldet.applied_flag = G_YES))
4111: OR EXISTS (SELECT 'Y' FROM oe_price_adjustments adj
4112: WHERE line.line_type_code = G_ORDER_LEVEL
4113: AND line.line_id = adj.header_id
4114: AND adj.applied_flag = G_YES
4115: AND adj.list_line_id NOT IN (SELECT ldet.created_from_list_line_id FROM qp_npreq_ldets_tmp ldet
4116: WHERE ldet.line_index = line.line_index
4117: AND ldet.pricing_status_code = G_STATUS_NEW
4118: AND ldet.process_code IN (G_STATUS_NEW, G_STATUS_UPDATED)
4119: AND ldet.applied_flag = G_YES))
4352:
4353: --begin SL_latest
4354: CURSOR l_net_mod_index_cur(p_list_line_id NUMBER) IS
4355: SELECT DISTINCT ldet.line_index
4356: FROM qp_npreq_ldets_tmp ldet
4357: WHERE ldet.created_from_list_line_id = p_list_line_id
4358: AND pricing_status_code IN (G_STATUS_NEW, G_STATUS_UPDATED, G_STATUS_UNCHANGED);
4359:
4360: l_line_bucket_amt NUMBER := 0;
6876: END IF;
6877: IF l_ldet_line_dtl_index_tbl.COUNT > 0
6878: THEN
6879: FORALL i IN l_ldet_line_dtl_index_tbl.FIRST..l_ldet_line_dtl_index_tbl.LAST
6880: UPDATE qp_npreq_ldets_tmp
6881: SET adjustment_amount = l_ldet_adj_amt_tbl(i),
6882: operand_value = l_ldet_operand_value_tbl(i),
6883: line_quantity = l_ldet_line_quantity_tbl(i),
6884: applied_flag = l_ldet_applied_flag_tbl(i),
6899: END IF;
6900: END IF; --l_ldet_line_dtl_index_tbl.count > 0
6901:
6902: FORALL i IN l_ldet_line_dtl_index_tbl.FIRST..l_ldet_line_dtl_index_tbl.LAST
6903: INSERT INTO qp_npreq_ldets_tmp
6904: (
6905: line_detail_index
6906: , line_index
6907: , line_detail_type_code
7132: /*
7133: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
7134: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_lines_tmp_N1,LINE_TYPE_CODE,2
7135: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_lines_tmp_N2,LINE_TYPE_CODE,1
7136: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
7137: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,2
7138: */
7139: CURSOR l_bucket_price_cur(p_line_index NUMBER) IS -- 2892848
7140: SELECT ldet.created_from_list_line_id
7133: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
7134: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_lines_tmp_N1,LINE_TYPE_CODE,2
7135: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_lines_tmp_N2,LINE_TYPE_CODE,1
7136: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
7137: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,2
7138: */
7139: CURSOR l_bucket_price_cur(p_line_index NUMBER) IS -- 2892848
7140: SELECT ldet.created_from_list_line_id
7141: , line.line_index line_ind
7169: , line.rounding_factor
7170: , G_LINE_LEVEL line_type
7171: , 'N' is_max_frt
7172: , ldet.net_amount_flag
7173: FROM qp_npreq_ldets_tmp ldet, qp_npreq_lines_tmp line
7174: --where line.line_index = p_line_index -- 2892848
7175: WHERE ldet.line_index = line.line_index -- 2892848
7176: --and ldet.line_index = line.line_index --2892848
7177: AND line.price_flag IN (G_YES, G_PHASE, G_CALCULATE_ONLY)
7228: , line.rounding_factor
7229: , G_ORDER_LEVEL line_type
7230: , 'N' is_max_frt
7231: , ldet.net_amount_flag
7232: FROM qp_npreq_ldets_tmp ldet, qp_npreq_lines_tmp line
7233: , qp_npreq_lines_tmp line1
7234: --where line.line_index = p_line_index -- 2892848
7235: --and ldet.line_index = line1.line_index -- 2892848
7236: WHERE ldet.line_index = line1.line_index -- 2892848
7262:
7263: CURSOR l_chk_backcal_adj_exist_cur(p_line_index NUMBER) IS
7264: SELECT ldet.line_detail_index
7265: , ldet.adjustment_amount
7266: FROM qp_npreq_ldets_tmp ldet
7267: WHERE line_index = p_line_index
7268: AND calculation_code = G_BACK_CALCULATE
7269: AND applied_flag = G_YES
7270: AND updated_flag = G_YES;
7302: l_amount_changed NUMBER := g_miss_num; --FND_API.G_MISS_NUM;
7303: l_adjustment_amount NUMBER := g_miss_num; --FND_API.G_MISS_NUM;
7304: l_operand_value NUMBER := g_miss_num; --FND_API.G_MISS_NUM;
7305: l_BACK_CALCULATE_START_TYPE VARCHAR2(30) := G_BACK_CALCULATION_STS_NONE; -- 2892848
7306: --l_prev_bucket qp_npreq_ldets_tmp.PRICING_GROUP_SEQUENCE%TYPE := g_miss_num;--FND_API.G_MISS_NUM;
7307: l_back_calc_dtl_index NUMBER := 0;
7308: l_back_calc_adj_amt NUMBER := 0;
7309:
7310: l_ldet_index_tbl QP_PREQ_GRP.NUMBER_TYPE;
7361: */
7362: --begin SL_latest
7363: CURSOR l_net_mod_index_cur(p_list_line_id NUMBER) IS
7364: SELECT DISTINCT ldet.line_index
7365: FROM qp_npreq_ldets_tmp ldet
7366: WHERE ldet.created_from_list_line_id = p_list_line_id
7367: AND pricing_status_code IN (G_STATUS_NEW, G_STATUS_UPDATED, G_STATUS_UNCHANGED);
7368:
7369: l_line_bucket_amt NUMBER := NULL;
8719: ||' count '|| l_ldet_tbl.COUNT);
8720: END IF;
8721:
8722: /*
8723: INDX,QP_PREQ_PUB.calculate_price.upd1,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
8724: */
8725: UPDATE qp_npreq_ldets_tmp SET
8726: adjustment_amount = l_ldet_tbl(x).adjustment_amount,
8727: applied_flag = l_ldet_tbl(x).applied_flag,
8721:
8722: /*
8723: INDX,QP_PREQ_PUB.calculate_price.upd1,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
8724: */
8725: UPDATE qp_npreq_ldets_tmp SET
8726: adjustment_amount = l_ldet_tbl(x).adjustment_amount,
8727: applied_flag = l_ldet_tbl(x).applied_flag,
8728: line_quantity = l_ldet_tbl(x).priced_quantity,
8729: --included for freight charge functionality
8920: AND l_back_calc_dtl_index <>
8921: l_back_calc_ret_rec.line_detail_index
8922: THEN
8923: --delete the existing back calculated adj
8924: UPDATE qp_npreq_ldets_tmp
8925: SET applied_flag = G_NO,
8926: calculation_code = NULL,
8927: pricing_status_code = G_STATUS_DELETED,
8928: pricing_status_text =
8931:
8932: END IF;
8933:
8934: /*
8935: INDX,QP_PREQ_PUB.calculate_price.upd2,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
8936: */
8937: UPDATE qp_npreq_ldets_tmp ldet
8938: SET ldet.operand_value =
8939: l_back_calc_ret_rec.operand_value
8933:
8934: /*
8935: INDX,QP_PREQ_PUB.calculate_price.upd2,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
8936: */
8937: UPDATE qp_npreq_ldets_tmp ldet
8938: SET ldet.operand_value =
8939: l_back_calc_ret_rec.operand_value
8940: , ldet.adjustment_amount =
8941: l_back_calc_ret_rec.adjustment_amount
9088: QP_PREQ_GRP.engine_debug('updating order lvl adj amt: '||
9089: l_ord_dtl_index_tbl.COUNT);
9090: END IF;
9091: FORALL i IN l_ord_dtl_index_tbl.FIRST..l_ord_dtl_index_tbl.LAST
9092: UPDATE qp_npreq_ldets_tmp
9093: SET adjustment_amount = l_ord_adj_amt_tbl(i)
9094: WHERE line_detail_index = l_ord_dtl_index_tbl(i);
9095: END IF;
9096:
9305: --mark the engine passed relationships of manual PBH as deleted
9306: --if the parent PBH line is deleted
9307: UPDATE qp_npreq_rltd_lines_tmp SET pricing_status_code = G_STATUS_DELETED
9308: WHERE line_detail_index IN (SELECT line_detail_index
9309: FROM qp_npreq_ldets_tmp ldet
9310: WHERE (ldet.process_code = G_STATUS_DELETED
9311: OR ldet.pricing_status_code = G_STATUS_DELETED))
9312: --fix for bug 2515762 automatic overrideable break
9313: -- and ldet.automatic_flag = G_NO)
9314: AND pricing_status_code = G_STATUS_NEW
9315: AND relationship_type_code = G_PBH_LINE;
9316:
9317: --mark the engine returned manual child PBH lines as deleted
9318: UPDATE qp_npreq_ldets_tmp ldet SET pricing_status_code = G_STATUS_DELETED
9319: WHERE ldet.line_detail_index IN
9320: (SELECT rltd.related_line_detail_index
9321: FROM qp_npreq_rltd_lines_tmp rltd
9322: WHERE rltd.relationship_type_code = G_PBH_LINE
9363: qualifier_value
9364: FROM
9365: qp_npreq_rltd_lines_tmp rltd,
9366: qp_npreq_lines_tmp line,
9367: qp_npreq_ldets_tmp ldet,
9368: qp_npreq_ldets_tmp ldet_pbh,
9369: qp_pricing_attributes qpa
9370: WHERE
9371: rltd.pricing_status_code = G_STATUS_NEW
9364: FROM
9365: qp_npreq_rltd_lines_tmp rltd,
9366: qp_npreq_lines_tmp line,
9367: qp_npreq_ldets_tmp ldet,
9368: qp_npreq_ldets_tmp ldet_pbh,
9369: qp_pricing_attributes qpa
9370: WHERE
9371: rltd.pricing_status_code = G_STATUS_NEW
9372: AND ldet.line_detail_index =
9441: nvl(ldet.line_quantity,
9442: nvl(line.priced_quantity, line.line_quantity))
9443: FROM
9444: qp_npreq_lines_tmp line,
9445: qp_npreq_ldets_tmp ldet,
9446: qp_npreq_ldets_tmp ldet_pbh,
9447: qp_pricing_attributes qpa
9448: WHERE
9449: ldet.line_detail_index = rltd.related_line_detail_index
9442: nvl(line.priced_quantity, line.line_quantity))
9443: FROM
9444: qp_npreq_lines_tmp line,
9445: qp_npreq_ldets_tmp ldet,
9446: qp_npreq_ldets_tmp ldet_pbh,
9447: qp_pricing_attributes qpa
9448: WHERE
9449: ldet.line_detail_index = rltd.related_line_detail_index
9450: AND ldet_pbh.line_detail_index = rltd.line_detail_index
9454: AND ldet.pricing_status_code = G_STATUS_UNCHANGED
9455: AND rltd.relationship_type_code = G_PBH_LINE
9456: AND rltd.pricing_status_code = G_STATUS_NEW)
9457: WHERE rltd.line_detail_index IN (SELECT ldet.line_detail_index
9458: FROM qp_npreq_ldets_tmp ldet
9459: WHERE ldet.process_code = G_STATUS_NEW
9460: AND ldet.pricing_status_code = G_STATUS_UNCHANGED
9461: AND ldet.created_from_list_line_type = G_PRICE_BREAK_TYPE)
9462: AND rltd.relationship_type_code = G_PBH_LINE
9493: , X_RETURN_STATUS OUT NOCOPY VARCHAR2
9494: , X_RETURN_STATUS_TEXT OUT NOCOPY VARCHAR2) IS
9495:
9496: /*
9497: INDX,QP_PREQ_PUB.process_adjustments.l_duplicate_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
9498: INDX,QP_PREQ_PUB.process_adjustments.l_duplicate_cur,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
9499: */
9500: CURSOR l_duplicate_cur IS
9501: SELECT ldeta.line_detail_index
9494: , X_RETURN_STATUS_TEXT OUT NOCOPY VARCHAR2) IS
9495:
9496: /*
9497: INDX,QP_PREQ_PUB.process_adjustments.l_duplicate_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
9498: INDX,QP_PREQ_PUB.process_adjustments.l_duplicate_cur,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
9499: */
9500: CURSOR l_duplicate_cur IS
9501: SELECT ldeta.line_detail_index
9502: , ldeta.line_index
9505: , ldeta.process_code
9506: , ldeta.pricing_status_text
9507: , ldeta.applied_flag
9508: , ldeta.updated_flag
9509: FROM qp_npreq_ldets_tmp ldeta
9510: WHERE --ldeta.pricing_status_code = 'X'
9511: ldeta.process_code = G_STATUS_NEW
9512: AND ldeta.created_from_list_line_id IN
9513: (SELECT ldetb.created_from_list_line_id
9510: WHERE --ldeta.pricing_status_code = 'X'
9511: ldeta.process_code = G_STATUS_NEW
9512: AND ldeta.created_from_list_line_id IN
9513: (SELECT ldetb.created_from_list_line_id
9514: FROM qp_npreq_ldets_tmp ldetb
9515: WHERE ldetb.created_from_list_line_id =
9516: ldeta.created_from_list_line_id
9517: AND ldetb.line_index = ldeta.line_index
9518: AND ldetb.process_code = G_STATUS_NEW
9538: SELECT line_index
9539: , created_from_list_line_id
9540: , pricing_status_code, applied_flag
9541: , updated_flag, operand_value
9542: FROM qp_npreq_ldets_tmp
9543: WHERE PRICING_STATUS_CODE = G_STATUS_UNCHANGED;
9544:
9545: lrec lcur%ROWTYPE;
9546:
9544:
9545: lrec lcur%ROWTYPE;
9546:
9547: /*
9548: INDX,QP_PREQ_PUB.process_adjustments.lcur1,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
9549: INDX,QP_PREQ_PUB.process_adjustments.lcur1,qp_npreq_ldets_tmp_N3,PRICING_STATUS_CODE,4
9550: */
9551: CURSOR lcur1 IS
9552: SELECT created_from_list_line_id
9545: lrec lcur%ROWTYPE;
9546:
9547: /*
9548: INDX,QP_PREQ_PUB.process_adjustments.lcur1,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
9549: INDX,QP_PREQ_PUB.process_adjustments.lcur1,qp_npreq_ldets_tmp_N3,PRICING_STATUS_CODE,4
9550: */
9551: CURSOR lcur1 IS
9552: SELECT created_from_list_line_id
9553: , line_detail_index
9556: , applied_flag
9557: , updated_flag
9558: , pricing_status_code
9559: , process_code
9560: FROM qp_npreq_ldets_tmp
9561: ORDER BY line_index;
9562:
9563: lrec1 lcur1%ROWTYPE;
9564:
9591:
9592: ----------------------------------------------------------------------------
9593: --The following code will process the information returned by the
9594: --pricing engine in the temporary tables, compare the adjustments passes
9595: --by the calling application and mark the temporary table qp_npreq_ldets_tmp
9596: --field process_code with a value G_STATUS_NEW
9597: ----------------------------------------------------------------------------
9598: IF l_debug = FND_API.G_TRUE THEN
9599: QP_PREQ_GRP.ENGINE_DEBUG('START PROCESS ADJUSTMENTS '|| p_pricing_event);
9601: END IF;
9602: --Step 0: Update all user passed adjustments to process_code 'DELETED'
9603: --to start with
9604:
9605: UPDATE qp_npreq_ldets_tmp ldet SET ldet.process_code = G_STATUS_DELETED
9606: WHERE ldet.pricing_status_code = G_STATUS_UNCHANGED;
9607:
9608: --First Step: Retain the adjustments input by user
9609: --which are in the phase which do not belong to the
9612:
9613: /*
9614: INDX,QP_PREQ_PUB.process_adjustments.upd1,QP_EVENT_PHASES_U1,PRICING_EVENT_CODE,1
9615: */
9616: UPDATE qp_npreq_ldets_tmp ldet SET ldet.process_code = G_STATUS_NEW
9617: WHERE ldet.pricing_status_code = G_STATUS_UNCHANGED AND
9618: ldet.applied_flag = G_YES AND
9619: ldet.pricing_phase_id NOT IN (SELECT ev.pricing_phase_id
9620: FROM qp_event_phases ev, qp_pricing_phases ph
9640: upd2 indxno index used
9641: */
9642: --bug 2264566 update the child lines of manual PBH which get
9643: --passed with applied_flag null and updated_flag = 'Y'
9644: UPDATE qp_npreq_ldets_tmp ldet SET ldet.process_code = G_STATUS_NEW
9645: WHERE ldet.pricing_status_code = G_STATUS_UNCHANGED AND
9646: --fix for bug 2515762 automatic overrideable break
9647: ldet.updated_flag = G_YES;
9648: -- ((ldet.updated_flag = G_YES and ldet.applied_flag = G_YES)
9657: QP_PREQ_GRP.ENGINE_DEBUG('PROCESS ADJUSTMENTS: Step1.5');
9658: END IF;
9659: --summary line adjustments with price flag 'N' need to be considered for calc
9660: --reqt from contracts for performance
9661: UPDATE qp_npreq_ldets_tmp ldet SET ldet.process_code = G_STATUS_NEW
9662: WHERE ldet.line_index IN
9663: (SELECT line.line_index FROM qp_npreq_lines_tmp line
9664: WHERE line.line_type_code = G_ORDER_LEVEL
9665: AND line.price_flag = G_NO);
9674:
9675: /*
9676: upd3 indxno index used
9677: */
9678: UPDATE qp_npreq_ldets_tmp ldet SET ldet.process_code = G_STATUS_DELETED
9679: WHERE ldet.process_code = G_STATUS_NEW
9680: AND ldet.pricing_status_code NOT IN
9681: (G_STATUS_NEW, G_STATUS_UNCHANGED); -- and ldet.applied_flag = 'Y';
9682:
9797: i := l_dup_adj_tbl.FIRST;
9798: WHILE i IS NOT NULL LOOP
9799:
9800: /*
9801: INDX,QP_PREQ_PUB.process_adjustments.upd4,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
9802: */
9803: UPDATE qp_npreq_ldets_tmp SET
9804: process_code = l_dup_adj_tbl(i).process_code
9805: , pricing_status_code = l_dup_adj_tbl(i).pricing_status_code
9799:
9800: /*
9801: INDX,QP_PREQ_PUB.process_adjustments.upd4,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
9802: */
9803: UPDATE qp_npreq_ldets_tmp SET
9804: process_code = l_dup_adj_tbl(i).process_code
9805: , pricing_status_code = l_dup_adj_tbl(i).pricing_status_code
9806: , pricing_status_text = l_dup_adj_tbl(i).pricing_status_text
9807: WHERE line_detail_index = l_dup_adj_tbl(i).line_detail_index;
9948: CURSOR l_child_break_cur IS
9949: SELECT /*+ ORDERED index(ldet QP_PREQ_LDETS_TMP_U1) */ldet.process_code,
9950: rltd.related_line_detail_index
9951: FROM qp_npreq_rltd_lines_tmp rltd,
9952: -- qp_npreq_ldets_tmp ldet1,
9953: qp_npreq_ldets_tmp ldet
9954: WHERE rltd.relationship_type_code = G_PBH_LINE
9955: AND rltd.pricing_status_code = G_STATUS_NEW
9956: AND ldet.line_index = rltd.line_index
9949: SELECT /*+ ORDERED index(ldet QP_PREQ_LDETS_TMP_U1) */ldet.process_code,
9950: rltd.related_line_detail_index
9951: FROM qp_npreq_rltd_lines_tmp rltd,
9952: -- qp_npreq_ldets_tmp ldet1,
9953: qp_npreq_ldets_tmp ldet
9954: WHERE rltd.relationship_type_code = G_PBH_LINE
9955: AND rltd.pricing_status_code = G_STATUS_NEW
9956: AND ldet.line_index = rltd.line_index
9957: AND ldet.line_detail_index = rltd.line_detail_index
10003: IF l_debug = FND_API.G_TRUE THEN
10004: QP_PREQ_GRP.engine_debug('before update');
10005: END IF;
10006: FORALL j IN l_line_dtl_index_tbl.FIRST..l_line_dtl_index_tbl.LAST
10007: UPDATE qp_npreq_ldets_tmp
10008: SET process_code = l_child_sts_code_tbl(j)
10009: WHERE line_detail_index = l_line_dtl_index_tbl(j);
10010: IF l_debug = FND_API.G_TRUE THEN
10011: QP_PREQ_GRP.engine_debug('after update');
10068: ldet.group_amount,
10069: line.parent_price
10070: FROM qp_npreq_lines_tmp line,
10071: qp_npreq_rltd_lines_tmp rltd,
10072: qp_npreq_ldets_tmp ldet,
10073: qp_pricing_attributes qpa,
10074: qp_npreq_line_attrs_tmp attr
10075: WHERE line.pricing_status_code IN (G_STATUS_UNCHANGED, G_STATUS_UPDATED)
10076: AND line.price_flag = G_CALCULATE_ONLY
10363: || l_line_quantity_tbl.FIRST ||' last '
10364: || l_line_quantity_tbl.LAST);
10365: END IF;
10366: FORALL i IN l_line_quantity_tbl.FIRST..l_line_quantity_tbl.LAST
10367: UPDATE qp_npreq_ldets_tmp
10368: SET line_quantity = l_line_quantity_tbl(i)
10369: , applied_flag = l_applied_flag_tbl(i)
10370: , adjustment_amount = l_adj_amt_tbl(i)
10371: WHERE line_detail_index = l_line_dtl_index_tbl(i);
10391: IF l_debug = FND_API.G_TRUE THEN
10392: QP_PREQ_GRP.engine_debug('update PBH ');
10393: END IF;
10394: FORALL i IN x_line_index_tbl.FIRST..x_line_index_tbl.LAST
10395: UPDATE qp_npreq_ldets_tmp
10396: SET line_quantity = l_total_qty_tbl(i)
10397: , applied_flag = G_YES
10398: , adjustment_amount = x_list_price_tbl(i)
10399: WHERE line_index = x_line_index_tbl(i)
10402: (G_PRICE_LIST_HEADER, G_AGR_LIST_HEADER)
10403: AND x_pricing_sts_code_tbl(i) = G_STATUS_UPDATED;
10404: END IF; --x_line_index_tbl.COUNT
10405:
10406: UPDATE qp_npreq_ldets_tmp
10407: SET applied_flag = G_NO
10408: WHERE created_from_list_line_type = G_PRICE_BREAK_TYPE
10409: AND created_from_list_type_code IN
10410: (G_PRICE_LIST_HEADER, G_AGR_LIST_HEADER)
10519: , line.rounding_factor
10520: , line.uom_quantity service_duration
10521: , line.processing_order
10522: , line.parent_price
10523: FROM qp_npreq_lines_tmp line, qp_npreq_ldets_tmp ldet
10524: WHERE line.usage_pricing_type = QP_PREQ_GRP.G_BILLING_TYPE
10525: AND ldet.line_index = line.line_index
10526: AND line.price_flag = G_CALCULATE_ONLY
10527: AND nvl(processing_order, 1) = p_processing_order
10632: IF l_debug = FND_API.G_TRUE THEN
10633: QP_PREQ_GRP.engine_debug('Begin Usage Pricing LOOP1');
10634: END IF;
10635: FORALL i IN l_line_dtl_index_tbl.FIRST..l_line_dtl_index_tbl.LAST
10636: UPDATE qp_npreq_ldets_tmp
10637: SET adjustment_amount = l_adj_amt_tbl(i)
10638: WHERE line_detail_index = l_line_dtl_index_tbl(i);
10639: END IF; --l_line_dtl_index_tbl.COUNT
10640:
10745: IF l_debug = FND_API.G_TRUE THEN
10746: QP_PREQ_GRP.engine_debug('Begin Usage Pricing LOOP2');
10747: END IF;
10748: FORALL i IN l_line_dtl_index_tbl.FIRST..l_line_dtl_index_tbl.LAST
10749: UPDATE qp_npreq_ldets_tmp
10750: SET adjustment_amount = l_adj_amt_tbl(i)
10751: WHERE line_detail_index = l_line_dtl_index_tbl(i);
10752: END IF; --l_line_dtl_index_tbl.COUNT
10753:
10931: oldprg.updated_flag
10932: FROM qp_npreq_lines_tmp buyline
10933: , qp_event_phases ev
10934: , qp_pricing_phases ph
10935: , qp_npreq_ldets_tmp oldprg
10936: , qp_npreq_rltd_lines_tmp oldrltd
10937: , qp_npreq_ldets_tmp oldfgdis
10938: , qp_npreq_lines_tmp oldfreeline
10939: --where G_REQUEST_TYPE_CODE <> 'ONT'
10933: , qp_event_phases ev
10934: , qp_pricing_phases ph
10935: , qp_npreq_ldets_tmp oldprg
10936: , qp_npreq_rltd_lines_tmp oldrltd
10937: , qp_npreq_ldets_tmp oldfgdis
10938: , qp_npreq_lines_tmp oldfreeline
10939: --where G_REQUEST_TYPE_CODE <> 'ONT'
10940: --bug 3085453 handle pricing availability UI
10941: -- they pass reqtype ONT and insert adj into ldets
11185: FOR i IN l_mark_prg_delete_index.FIRST..l_mark_prg_delete_index.LAST
11186: LOOP
11187: BEGIN
11188: SELECT created_from_list_line_id INTO l_prg_list_line_id
11189: FROM qp_npreq_ldets_tmp
11190: WHERE line_detail_index = l_mark_prg_delete_index(i);
11191: QP_PREQ_GRP.engine_debug('Mark delete prg '|| l_mark_prg_delete_index(i)
11192: ||' list_line_id '|| l_prg_list_line_id);
11193: EXCEPTION
11197: END LOOP;
11198: END IF; --l_debug
11199: IF QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = 'N' THEN
11200: FORALL i IN l_mark_prg_delete_index.FIRST..l_mark_prg_delete_index.LAST
11201: UPDATE qp_npreq_ldets_tmp SET pricing_status_code = G_STATUS_DELETED
11202: WHERE line_detail_index = l_mark_prg_delete_index(i);
11203: END IF;
11204: END IF; --l_mark_prg_delete_index.COUNT
11205:
11209: FOR i IN l_mark_fgdis_delete_index.FIRST..l_mark_fgdis_delete_index.LAST
11210: LOOP
11211: BEGIN
11212: SELECT created_from_list_line_id INTO l_prg_list_line_id
11213: FROM qp_npreq_ldets_tmp
11214: WHERE line_detail_index = l_mark_fgdis_delete_index(i);
11215: QP_PREQ_GRP.engine_debug('Mark delete fgdis '|| l_mark_fgdis_delete_index
11216: (i)
11217: ||' list_line_id '|| l_prg_list_line_id);
11221: END;
11222: END LOOP;
11223: END IF; --l_debug
11224: FORALL i IN l_mark_fgdis_delete_index.FIRST..l_mark_fgdis_delete_index.LAST
11225: UPDATE qp_npreq_ldets_tmp SET pricing_status_code = G_STATUS_DELETED
11226: WHERE line_detail_index = l_mark_fgdis_delete_index(i);
11227: END IF; --l_mark_fgdis_delete_index.count
11228: IF l_debug = FND_API.G_TRUE THEN
11229: QP_PREQ_GRP.engine_debug('End Update_prg_pricing_status');
11349: newfgdis.created_from_list_line_id newfgdis_list_line_id,
11350: newfgdis.operand_value || newfgdis.operand_calculation_code newfgdis_operand
11351: FROM qp_npreq_lines_tmp oldfreeline
11352: , qp_npreq_rltd_lines_tmp newrltd
11353: , qp_npreq_ldets_tmp newfgdis
11354: , qp_npreq_lines_tmp newfgline
11355: , qp_npreq_line_attrs_tmp oldfreeitem
11356: , qp_npreq_line_attrs_tmp newfgitem
11357: --where G_REQUEST_TYPE_CODE = 'ONT'
11469: newfgdis.created_from_list_line_id newfgdis_list_line_id,
11470: newfgdis.operand_value || newfgdis.operand_calculation_code newfgdis_operand
11471: FROM qp_npreq_lines_tmp oldfreeline
11472: , qp_npreq_rltd_lines_tmp newrltd
11473: , qp_npreq_ldets_tmp newfgdis
11474: , qp_npreq_lines_tmp newfgline
11475: , qp_npreq_line_attrs_tmp newfgitem
11476: , qp_npreq_line_attrs_tmp oldfreeitem
11477: --WHERE G_REQUEST_TYPE_CODE <> 'ONT'
11499:
11500: CURSOR l_updated_prg_fg_cur IS
11501: SELECT /*+ INDEX(prg OE_PRICE_ADJUSTMENTS_N2) */ rltd.related_line_index
11502: FROM qp_npreq_lines_tmp buyline, oe_price_adjustments prg,
11503: qp_npreq_ldets_tmp ldet, qp_npreq_rltd_lines_tmp rltd
11504: --WHERE G_REQUEST_TYPE_CODE = 'ONT'
11505: --bug 3085453 handle pricing availability UI
11506: -- they pass reqtype ONT and insert adj into ldets
11507: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = G_YES
11518: AND rltd.pricing_status_code = G_STATUS_NEW
11519: UNION
11520: SELECT /*+ INDEX(prg OE_PRICE_ADJUSTMENTS_N1) */ rltd.related_line_index
11521: FROM qp_npreq_lines_tmp buyline, oe_price_adjustments prg,
11522: qp_npreq_ldets_tmp ldet, qp_npreq_rltd_lines_tmp rltd
11523: --WHERE G_REQUEST_TYPE_CODE = 'ONT'
11524: --bug 3085453 handle pricing availability UI
11525: -- they pass reqtype ONT and insert adj into ldets
11526: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = G_YES
11538: AND rltd.pricing_status_code = G_STATUS_NEW
11539: UNION
11540: -- hint added for 5575718
11541: SELECT /*+ ORDERED */ rltd.related_line_index
11542: FROM qp_npreq_lines_tmp buyline, qp_npreq_ldets_tmp prg,
11543: qp_npreq_ldets_tmp ldet, qp_npreq_rltd_lines_tmp rltd
11544: --WHERE G_REQUEST_TYPE_CODE <> 'ONT'
11545: --bug 3085453 handle pricing availability UI
11546: -- they pass reqtype ONT and insert adj into ldets
11539: UNION
11540: -- hint added for 5575718
11541: SELECT /*+ ORDERED */ rltd.related_line_index
11542: FROM qp_npreq_lines_tmp buyline, qp_npreq_ldets_tmp prg,
11543: qp_npreq_ldets_tmp ldet, qp_npreq_rltd_lines_tmp rltd
11544: --WHERE G_REQUEST_TYPE_CODE <> 'ONT'
11545: --bug 3085453 handle pricing availability UI
11546: -- they pass reqtype ONT and insert adj into ldets
11547: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG <> G_YES
11592: G_prg_unch_process_sts_tbl.DELETE;
11593:
11594: BEGIN
11595: SELECT 'Y' INTO l_Process_PRG
11596: FROM qp_npreq_ldets_tmp
11597: WHERE created_from_list_line_type = G_PROMO_GOODS_DISCOUNT
11598: AND pricing_status_code = G_STATUS_NEW
11599: AND applied_flag = 'Y'
11600: AND ROWNUM = 1;
11650: AND adj.line_id IS NULL
11651: AND adj.list_line_type_code = G_PROMO_GOODS_DISCOUNT
11652: AND adj.updated_flag = G_YES
11653: UNION
11654: SELECT 'Y' FROM qp_npreq_ldets_tmp adj
11655: WHERE adj.created_from_list_line_type = G_PROMO_GOODS_DISCOUNT
11656: AND adj.pricing_status_code = G_STATUS_UNCHANGED
11657: AND adj.updated_flag = G_YES);
11658: END IF; --l_PROCESS_PRG
11699: ||'prg listlineid '|| cl.list_line_id ||' updatedflag '|| cl.updated_flag
11700: ||' buylineid '|| cl.line_id ||' prgphaseid '|| cl.pricing_phase_id);
11701: FOR fg IN
11702: (SELECT rltd.related_line_index, ldet.created_from_list_line_id
11703: FROM qp_npreq_ldets_tmp ldet, qp_npreq_rltd_lines_tmp rltd
11704: WHERE ldet.line_index = cl.line_index
11705: AND ldet.pricing_status_code = G_STATUS_NEW
11706: AND ldet.applied_flag = G_YES
11707: AND ldet.created_from_list_line_id = cl.list_line_id
11776: THEN
11777: FOR cl IN (SELECT newprg.line_index line_ind
11778: , newprg.created_from_list_line_id
11779: FROM qp_npreq_lines_tmp oldfgline
11780: , qp_npreq_ldets_tmp newprg
11781: WHERE --ldet.line_index = oldfgline.line_index
11782: instr(oldfgline.process_status
11783: , G_BUYLINE || newprg.line_index || G_PROMO_GOODS_DISCOUNT) > 0
11784: AND newprg.applied_flag = G_YES
11799: --PRG modifiers against its buy line
11800: UPDATE qp_npreq_lines_tmp oldfgline
11801: SET oldfgline.process_status = G_STATUS_DELETED
11802: WHERE NOT EXISTS (SELECT newprg.line_index
11803: FROM qp_npreq_ldets_tmp newprg
11804: --, qp_npreq_line_attrs_tmp newfgitem <-- commented out, sql repos
11805: WHERE --G_REQUEST_TYPE_CODE = 'ONT'
11806: newprg.pricing_status_code = G_STATUS_NEW
11807: AND newprg.applied_flag = G_YES
12117: CURSOR l_buyline_price_flag_cur IS
12118: SELECT /*+ ORDERED USE_NL(buyline prg dis)*/
12119: dis.created_from_list_line_id, prg.line_detail_index, buyline.price_flag, 'Y' is_ldet, dis.line_index
12120: FROM qp_npreq_rltd_lines_tmp rltd, qp_npreq_lines_tmp buyline
12121: , qp_npreq_ldets_tmp prg, qp_npreq_ldets_tmp dis
12122: WHERE rltd.pricing_status_code = G_STATUS_NEW
12123: AND rltd.relationship_type_code = G_GENERATED_LINE
12124: AND buyline.line_index = rltd.line_index
12125: AND prg.line_detail_index = rltd.line_detail_index
12147: IF l_debug = FND_API.G_TRUE THEN
12148: QP_PREQ_GRP.engine_debug('loop linedtl '|| c1.line_detail_index ||' rltddtl '
12149: || c1.related_line_detail_index ||' lineind '|| c1.line_index);
12150: END IF;
12151: FOR c2 IN (SELECT line_index, line_detail_index FROM qp_npreq_ldets_tmp
12152: WHERE line_detail_index = c1.line_detail_index
12153: AND pricing_status_code = G_STATUS_NEW
12154: AND created_from_list_line_type IN
12155: (G_OTHER_ITEM_DISCOUNT, G_PROMO_GOODS_DISCOUNT))
12194: OPEN l_buyline_price_flag_cur;
12195: FETCH l_buyline_price_flag_cur BULK COLLECT INTO
12196: G_buyline_list_line_id, G_buyline_price_flag;
12197: CLOSE l_buyline_price_flag_cur;
12198: UPDATE qp_npreq_ldets_tmp freegood_dis set buy_line_price_flag =
12199: (select buyline.price_flag
12200: from qp_npreq_rltd_lines_tmp rl, qp_npreq_lines_tmp buyline
12201: where rl.pricing_status_code = G_STATUS_NEW
12202: and rl.relationship_type_code = G_GENERATED_LINE
12286: created_from_list_line_id
12287: , line_quantity
12288: , line_detail_index
12289: , pricing_phase_id
12290: FROM qp_npreq_ldets_tmp ldets
12291: WHERE created_from_list_line_type = G_COUPON_ISSUE
12292: AND applied_flag = G_YES
12293: /*
12294: --you don't need to match the phase as this will be called after cleanup
12363: , ldet.accrual_flag
12364: , qpl.accrual_conversion_rate
12365: , qpl.estim_accrual_rate
12366: -- , qpl.benefit_qty
12367: FROM qp_npreq_ldets_tmp ldet, qp_npreq_line_attrs_tmp lattr, qp_list_lines qpl
12368: WHERE ldet.pricing_phase_id > 1
12369: AND ldet.pricing_status_code = G_STATUS_NEW
12370: AND ldet.line_index > -1
12371: AND ldet.created_from_list_line_type IN ('DIS', 'SUR', 'FREIGHT_CHARGE',
12456: IF l_debug = FND_API.G_TRUE THEN
12457: QP_PREQ_GRP.engine_debug('Updating recurring qty '|| l_recur_dtl_index_tbl.COUNT);
12458: END IF;
12459: FORALL i IN l_recur_dtl_index_tbl.FIRST .. l_recur_dtl_index_tbl.LAST
12460: UPDATE qp_npreq_ldets_tmp recur SET operand_value = -- shu fix 2702384
12461: l_recur_tot_benefit_qty_tbl(i)
12462: , benefit_qty = l_recur_benefit_qty_tbl(i) --Bug 2804053
12463: WHERE recur.line_detail_index = l_recur_dtl_index_tbl(i);
12464: END IF; --l_recur_dtl_index_tbl.COUNT
12755: FROM qp_npreq_lines_tmp;
12756:
12757: /*
12758: INDX,QP_PREQ_GRP.populate_output.l_ldets_cur,QP_LIST_LINES_PK,LIST_LINE_ID,1
12759: INDX,QP_PREQ_GRP.populate_output.l_ldets_cur,qp_npreq_ldets_tmp_N4,PRICING_STATUS_CODE,1
12760: INDX,QP_PREQ_GRP.populate_output.l_ldets_cur,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
12761: */
12762: CURSOR l_ldets_cur(p_line_index NUMBER) IS
12763: SELECT /*+ ORDERED USE_NL(A B C) l_ldets_cur */
12816: b.EXPIRATION_PERIOD_UOM,
12817: b.COMMENTS,
12818: a.ORDER_QTY_OPERAND,
12819: a.ORDER_QTY_ADJ_AMT
12820: FROM qp_npreq_ldets_tmp a,
12821: QP_LIST_LINES b
12822: WHERE a.line_index = p_line_index
12823: AND a.CREATED_FROM_LIST_LINE_ID = b.LIST_LINE_ID
12824: AND a.PRICING_STATUS_CODE = G_STATUS_NEW;
12992: i PLS_INTEGER;
12993: j PLS_INTEGER;
12994:
12995: /*
12996: INDX,QP_PREQ_PUB.price_request.lcur1,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
12997: INDX,QP_PREQ_PUB.price_request.lcur1,qp_npreq_ldets_tmp_N3,PRICING_STATUS_CODE,4
12998: */
12999: CURSOR lcur1 IS
13000: SELECT ldet.created_from_list_line_id
12993: j PLS_INTEGER;
12994:
12995: /*
12996: INDX,QP_PREQ_PUB.price_request.lcur1,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
12997: INDX,QP_PREQ_PUB.price_request.lcur1,qp_npreq_ldets_tmp_N3,PRICING_STATUS_CODE,4
12998: */
12999: CURSOR lcur1 IS
13000: SELECT ldet.created_from_list_line_id
13001: , line.line_index line_ind
13012: , ldet.updated_flag
13013: , ldet.calculation_code
13014: , line.qualifiers_exist_flag
13015: , ldet.pricing_group_sequence bucket
13016: FROM qp_npreq_ldets_tmp ldet, qp_npreq_lines_tmp line
13017: WHERE line.line_index = ldet.line_index
13018: ORDER BY line_ind;
13019:
13020: --3169430
13813: --this needs to be done for OC/ASO as they pass in adj as G_STATUS_UNCHANGED
13814: /*
13815: indxno index used
13816: */
13817: UPDATE qp_npreq_ldets_tmp
13818: SET pricing_status_code = G_STATUS_NEW
13819: WHERE process_code = G_STATUS_NEW
13820: --changes for bug 2264566
13821: --changed to populate w/updated_flag = Y for child lines of manualPBH
14121: END IF;
14122: /*
14123: indxno index used Ravi
14124: */
14125: UPDATE qp_npreq_ldets_tmp
14126: SET pricing_status_code = G_STATUS_NEW
14127: WHERE process_code = G_STATUS_NEW
14128: --changes for bug 2264566
14129: --changed to populate w/updated_flag = Y for child lines of manualPBH