[Home] [Help]
1: PACKAGE BODY QP_PREQ_PUB AS
2: /* $Header: QPXPPREB.pls 120.48.12020000.6 2013/01/31 18:34:46 jputta ship $ */
3:
4: l_debug VARCHAR2(3);
5: G_VERSION VARCHAR2(240) := '/* $Header: QPXPPREB.pls 120.48.12020000.6 2013/01/31 18:34:46 jputta ship $ */';
63: , PRICING_GROUP_SEQUENCE NUMBER
64: , LIST_LINE_NO VARCHAR2(240));
65:
66:
67: G_NOT_MAX_FRT_CHARGE CONSTANT VARCHAR2(100) := 'QP_PREQ_PUB:DELETED TO RETURN MAX/OVERRID FREIGHT CHARGE';
68: G_FREEZE_OVERRIDE_FLAG VARCHAR2(1) := '';
69: G_GSA_ENABLED_FLAG VARCHAR2(1) := NULL; --FND_API.G_MISS_CHAR;
70:
71: FUNCTION Get_Version RETURN VARCHAR2 IS
85: WHERE LIST_TYPE_CODE = 'PRL'
86: AND ROWNUM < 2;
87:
88: /*
89: INDX,QP_PREQ_PUB.initialize_constants.l_check_pricing_phase_exists,QP_PRICING_PHASES_U1,PRICING_PHASE_ID,1
90: INDX,QP_PREQ_PUB.initialize_constants.l_check_pricing_phase_exists,QP_EVENT_PHASES_U1,PRICING_EVENT_CODE,1
91: INDX,QP_PREQ_PUB.initialize_constants.l_check_pricing_phase_exists,QP_EVENT_PHASES_U1,PRICING_PHASE_ID,1
92: */
93: CURSOR l_check_pricing_phase_exists(p_event VARCHAR2) IS
86: AND ROWNUM < 2;
87:
88: /*
89: INDX,QP_PREQ_PUB.initialize_constants.l_check_pricing_phase_exists,QP_PRICING_PHASES_U1,PRICING_PHASE_ID,1
90: INDX,QP_PREQ_PUB.initialize_constants.l_check_pricing_phase_exists,QP_EVENT_PHASES_U1,PRICING_EVENT_CODE,1
91: INDX,QP_PREQ_PUB.initialize_constants.l_check_pricing_phase_exists,QP_EVENT_PHASES_U1,PRICING_PHASE_ID,1
92: */
93: CURSOR l_check_pricing_phase_exists(p_event VARCHAR2) IS
94: SELECT b.pricing_phase_id
87:
88: /*
89: INDX,QP_PREQ_PUB.initialize_constants.l_check_pricing_phase_exists,QP_PRICING_PHASES_U1,PRICING_PHASE_ID,1
90: INDX,QP_PREQ_PUB.initialize_constants.l_check_pricing_phase_exists,QP_EVENT_PHASES_U1,PRICING_EVENT_CODE,1
91: INDX,QP_PREQ_PUB.initialize_constants.l_check_pricing_phase_exists,QP_EVENT_PHASES_U1,PRICING_PHASE_ID,1
92: */
93: CURSOR l_check_pricing_phase_exists(p_event VARCHAR2) IS
94: SELECT b.pricing_phase_id
95: , nvl(b.user_freeze_override_flag, b.freeze_override_flag)
134: l_freeze_override_flag VARCHAR2(3);
135: l_null_eff_date_line NUMBER;
136: l_currency_code VARCHAR2(30);
137: l_status_text VARCHAR2(30);
138: l_routine VARCHAR2(50) := 'QP_PREQ_PUB.Initialize_Constants';
139: --sourcing volume attributes performance changes
140: l_source_qty_flag VARCHAR2(1);
141: l_source_amt_flag VARCHAR2(1);
142: l_null_price_req_code VARCHAR2(1) := G_NO;
210: OR adjusted_unit_price = FND_API.G_MISS_NUM;
211: EXCEPTION
212: WHEN OTHERS THEN
213: IF l_debug = FND_API.G_TRUE THEN
214: QP_PREQ_GRP.engine_debug('QP_PREQ_PUB: Init_cons update status '|| SQLERRM);
215: END IF;
216: x_return_status_text := SQLERRM;
217: RAISE E_OTHER_ERRORS;
218: END;
317:
318: END IF;
319: BEGIN
320: IF l_debug = FND_API.G_TRUE THEN
321: QP_PREQ_GRP.engine_debug('QP_PREQ_PUB before insert source line quantity ');
322: END IF;
323: --sourcing volume attributes performance changes
324: l_source_qty_flag := QP_BUILD_SOURCING_PVT.Is_Attribute_Used('VOLUME', 'PRICING_ATTRIBUTE10');
325: IF l_source_qty_flag = G_YES
394: , NULL
395: FROM qp_npreq_lines_tmp;
396:
397: IF l_debug = FND_API.G_TRUE THEN
398: QP_PREQ_GRP.engine_debug('QP_PREQ_PUB after insert source line quantity ');
399: END IF;
400: END IF; --sourcing volume attributes performance changes
401: EXCEPTION
402: WHEN OTHERS THEN
400: END IF; --sourcing volume attributes performance changes
401: EXCEPTION
402: WHEN OTHERS THEN
403: IF l_debug = FND_API.G_TRUE THEN
404: QP_PREQ_GRP.engine_debug('QP_PREQ_PUB init_cons sourceqty exp '|| SQLERRM);
405: END IF;
406: x_return_status_text := SQLERRM;
407: RAISE E_OTHER_ERRORS;
408: END;
410:
411:
412: BEGIN
413: IF l_debug = FND_API.G_TRUE THEN
414: QP_PREQ_GRP.engine_debug('QP_PREQ_PUB before insert source line amt ');
415: END IF;
416: --sourcing volume attributes performance changes
417: l_source_amt_flag := QP_BUILD_SOURCING_PVT.Is_Attribute_Used('VOLUME', 'PRICING_ATTRIBUTE12');
418: IF l_source_amt_flag = G_YES
485: , NULL
486: , NULL
487: FROM qp_npreq_lines_tmp;
488: IF l_debug = FND_API.G_TRUE THEN
489: QP_PREQ_GRP.engine_debug('QP_PREQ_PUB after insert source line amt ');
490: END IF;
491: END IF; --sourcing volume attributes performance changes
492: EXCEPTION
493: WHEN OTHERS THEN
491: END IF; --sourcing volume attributes performance changes
492: EXCEPTION
493: WHEN OTHERS THEN
494: IF l_debug = FND_API.G_TRUE THEN
495: QP_PREQ_GRP.engine_debug('QP_PREQ_PUB init_cons sourceamt exp '|| SQLERRM);
496: END IF;
497: x_return_status_text := SQLERRM;
498: RAISE E_OTHER_ERRORS;
499: END;
798: , x_return_status OUT NOCOPY VARCHAR2 --DEFAULT FND_API.G_RET_STS_SUCCESS
799: , x_return_status_text OUT NOCOPY VARCHAR2)
800: IS
801:
802: l_routine VARCHAR2(50) := 'QP_PREQ_PUB.CALCULATE_BUCKET_PRICE';
803: BEGIN
804: l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
805: IF l_debug = FND_API.G_TRUE THEN
806: QP_PREQ_GRP.ENGINE_DEBUG('begin calculate bucket price qty'
885: , x_process_code OUT NOCOPY BOOLEAN
886: , x_return_status OUT NOCOPY VARCHAR2 --DEFAULT FND_API.G_RET_STS_SUCCESS
887: , x_return_status_text OUT NOCOPY VARCHAR2) IS --DEFAULT FND_API.G_RET_STS_SUCCESS) IS
888:
889: l_routine VARCHAR2(50) := 'QP_PREQ_PUB.BACK_CALCULATE_PRICE';
890: ZERO_LIST_PRICE EXCEPTION;
891:
892: BEGIN
893: l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
1254: p_full_pricing_call IN VARCHAR2,
1255: x_return_status OUT NOCOPY VARCHAR2,
1256: x_return_status_text OUT NOCOPY VARCHAR2) IS
1257:
1258: l_routine VARCHAR2(100) := 'QP_PREQ_PUB.CHECK_ORDER_LINE_CHANGES';
1259:
1260: l_exist_changed_order_adj VARCHAR2(1) := G_NO;
1261: l_header_id NUMBER;
1262: l_ldet_sum_operand NUMBER := 0;
1276: --do this only if caller has passed changed lines
1277: --IF p_request_type_code = 'ONT'
1278: --bug 3085453 handle pricing availability UI
1279: -- they pass reqtype ONT and insert adj into ldets
1280: IF QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = G_YES
1281: AND p_full_pricing_call = G_NO
1282: THEN
1283: --this is for OM/OC direct insert performance code path
1284: --there cannot be manual adjustments on the order header
1367: END IF; --l_exist_changed_order_adj
1368: --ELSIF p_request_type_code <> 'ONT'
1369: --bug 3085453 handle pricing availability UI
1370: -- they pass reqtype ONT and insert adj into ldets
1371: ELSIF QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG <> G_YES
1372: AND p_full_pricing_call = G_NO THEN
1373: --this is for non-OM calling applications
1374: IF l_debug = FND_API.G_TRUE THEN
1375: QP_PREQ_GRP.engine_debug('starting order line check ');
1539: , NULL PRICING_EFFECTIVE_DATE
1540: , NULL BASE_CURRENCY_CODE
1541: --, line.line_quantity
1542: , adj.range_break_quantity line_quantity
1543: , nvl(adj.updated_flag, QP_PREQ_PUB.G_NO) updated_flag
1544: , NULL calculation_code
1545: , adj.CHANGE_REASON_CODE
1546: , adj.CHANGE_REASON_TEXT
1547: , adj.PRICE_ADJUSTMENT_ID
1553: , NULL BREAK_UOM_ATTRIBUTE
1554: , NULL PROCESS_CODE -- 3215497
1555: FROM qp_int_lines line, oe_price_adjustments adj,
1556: qp_list_headers_b qplh
1557: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
1558: AND line.process_status IN (QP_PREQ_PUB.G_STATUS_UPDATED,
1559: QP_PREQ_PUB.G_STATUS_NEW,
1560: QP_PREQ_PUB.G_STATUS_UNCHANGED,
1561: 'NEW' || QP_PREQ_PUB.G_STATUS_UNCHANGED,
1554: , NULL PROCESS_CODE -- 3215497
1555: FROM qp_int_lines line, oe_price_adjustments adj,
1556: qp_list_headers_b qplh
1557: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
1558: AND line.process_status IN (QP_PREQ_PUB.G_STATUS_UPDATED,
1559: QP_PREQ_PUB.G_STATUS_NEW,
1560: QP_PREQ_PUB.G_STATUS_UNCHANGED,
1561: 'NEW' || QP_PREQ_PUB.G_STATUS_UNCHANGED,
1562: 'OLD' || QP_PREQ_PUB.G_STATUS_UNCHANGED)
1555: FROM qp_int_lines line, oe_price_adjustments adj,
1556: qp_list_headers_b qplh
1557: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
1558: AND line.process_status IN (QP_PREQ_PUB.G_STATUS_UPDATED,
1559: QP_PREQ_PUB.G_STATUS_NEW,
1560: QP_PREQ_PUB.G_STATUS_UNCHANGED,
1561: 'NEW' || QP_PREQ_PUB.G_STATUS_UNCHANGED,
1562: 'OLD' || QP_PREQ_PUB.G_STATUS_UNCHANGED)
1563: AND adj.line_id = line.line_id
1556: qp_list_headers_b qplh
1557: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
1558: AND line.process_status IN (QP_PREQ_PUB.G_STATUS_UPDATED,
1559: QP_PREQ_PUB.G_STATUS_NEW,
1560: QP_PREQ_PUB.G_STATUS_UNCHANGED,
1561: 'NEW' || QP_PREQ_PUB.G_STATUS_UNCHANGED,
1562: 'OLD' || QP_PREQ_PUB.G_STATUS_UNCHANGED)
1563: AND adj.line_id = line.line_id
1564: AND line.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
1557: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
1558: AND line.process_status IN (QP_PREQ_PUB.G_STATUS_UPDATED,
1559: QP_PREQ_PUB.G_STATUS_NEW,
1560: QP_PREQ_PUB.G_STATUS_UNCHANGED,
1561: 'NEW' || QP_PREQ_PUB.G_STATUS_UNCHANGED,
1562: 'OLD' || QP_PREQ_PUB.G_STATUS_UNCHANGED)
1563: AND adj.line_id = line.line_id
1564: AND line.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
1565: , QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
1558: AND line.process_status IN (QP_PREQ_PUB.G_STATUS_UPDATED,
1559: QP_PREQ_PUB.G_STATUS_NEW,
1560: QP_PREQ_PUB.G_STATUS_UNCHANGED,
1561: 'NEW' || QP_PREQ_PUB.G_STATUS_UNCHANGED,
1562: 'OLD' || QP_PREQ_PUB.G_STATUS_UNCHANGED)
1563: AND adj.line_id = line.line_id
1564: AND line.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
1565: , QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
1566: , QP_PREQ_PUB.G_STATUS_UNCHANGED)
1560: QP_PREQ_PUB.G_STATUS_UNCHANGED,
1561: 'NEW' || QP_PREQ_PUB.G_STATUS_UNCHANGED,
1562: 'OLD' || QP_PREQ_PUB.G_STATUS_UNCHANGED)
1563: AND adj.line_id = line.line_id
1564: AND line.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
1565: , QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
1566: , QP_PREQ_PUB.G_STATUS_UNCHANGED)
1567: AND (line.price_flag IN (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
1568: OR nvl(line.processed_code, '0') = QP_PREQ_PUB.G_BY_ENGINE)
1561: 'NEW' || QP_PREQ_PUB.G_STATUS_UNCHANGED,
1562: 'OLD' || QP_PREQ_PUB.G_STATUS_UNCHANGED)
1563: AND adj.line_id = line.line_id
1564: AND line.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
1565: , QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
1566: , QP_PREQ_PUB.G_STATUS_UNCHANGED)
1567: AND (line.price_flag IN (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
1568: OR nvl(line.processed_code, '0') = QP_PREQ_PUB.G_BY_ENGINE)
1569: AND line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
1562: 'OLD' || QP_PREQ_PUB.G_STATUS_UNCHANGED)
1563: AND adj.line_id = line.line_id
1564: AND line.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
1565: , QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
1566: , QP_PREQ_PUB.G_STATUS_UNCHANGED)
1567: AND (line.price_flag IN (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
1568: OR nvl(line.processed_code, '0') = QP_PREQ_PUB.G_BY_ENGINE)
1569: AND line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
1570: AND (adj.updated_flag = QP_PREQ_PUB.G_YES
1563: AND adj.line_id = line.line_id
1564: AND line.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
1565: , QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
1566: , QP_PREQ_PUB.G_STATUS_UNCHANGED)
1567: AND (line.price_flag IN (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
1568: OR nvl(line.processed_code, '0') = QP_PREQ_PUB.G_BY_ENGINE)
1569: AND line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
1570: AND (adj.updated_flag = QP_PREQ_PUB.G_YES
1571: OR line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1564: AND line.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
1565: , QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
1566: , QP_PREQ_PUB.G_STATUS_UNCHANGED)
1567: AND (line.price_flag IN (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
1568: OR nvl(line.processed_code, '0') = QP_PREQ_PUB.G_BY_ENGINE)
1569: AND line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
1570: AND (adj.updated_flag = QP_PREQ_PUB.G_YES
1571: OR line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1572: OR (p_event_code IS NULL AND adj.updated_flag IS NULL)
1565: , QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
1566: , QP_PREQ_PUB.G_STATUS_UNCHANGED)
1567: AND (line.price_flag IN (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
1568: OR nvl(line.processed_code, '0') = QP_PREQ_PUB.G_BY_ENGINE)
1569: AND line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
1570: AND (adj.updated_flag = QP_PREQ_PUB.G_YES
1571: OR line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1572: OR (p_event_code IS NULL AND adj.updated_flag IS NULL)
1573: OR (adj.list_line_type_code = 'PRG'
1566: , QP_PREQ_PUB.G_STATUS_UNCHANGED)
1567: AND (line.price_flag IN (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
1568: OR nvl(line.processed_code, '0') = QP_PREQ_PUB.G_BY_ENGINE)
1569: AND line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
1570: AND (adj.updated_flag = QP_PREQ_PUB.G_YES
1571: OR line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1572: OR (p_event_code IS NULL AND adj.updated_flag IS NULL)
1573: OR (adj.list_line_type_code = 'PRG'
1574: AND adj.pricing_phase_id IN (SELECT ph.pricing_phase_id
1567: AND (line.price_flag IN (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
1568: OR nvl(line.processed_code, '0') = QP_PREQ_PUB.G_BY_ENGINE)
1569: AND line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
1570: AND (adj.updated_flag = QP_PREQ_PUB.G_YES
1571: OR line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1572: OR (p_event_code IS NULL AND adj.updated_flag IS NULL)
1573: OR (adj.list_line_type_code = 'PRG'
1574: AND adj.pricing_phase_id IN (SELECT ph.pricing_phase_id
1575: FROM qp_event_phases ev, qp_pricing_phases ph
1573: OR (adj.list_line_type_code = 'PRG'
1574: AND adj.pricing_phase_id IN (SELECT ph.pricing_phase_id
1575: FROM qp_event_phases ev, qp_pricing_phases ph
1576: WHERE ph.pricing_phase_id = ev.pricing_phase_id
1577: AND ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
1578: AND ph.freight_exists = QP_PREQ_PUB.G_YES)
1579: OR (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1580: AND instr(p_event_code, ev.pricing_event_code || ',') > 0
1581: --and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_YES
1574: AND adj.pricing_phase_id IN (SELECT ph.pricing_phase_id
1575: FROM qp_event_phases ev, qp_pricing_phases ph
1576: WHERE ph.pricing_phase_id = ev.pricing_phase_id
1577: AND ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
1578: AND ph.freight_exists = QP_PREQ_PUB.G_YES)
1579: OR (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1580: AND instr(p_event_code, ev.pricing_event_code || ',') > 0
1581: --and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_YES
1582: --or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
1575: FROM qp_event_phases ev, qp_pricing_phases ph
1576: WHERE ph.pricing_phase_id = ev.pricing_phase_id
1577: AND ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
1578: AND ph.freight_exists = QP_PREQ_PUB.G_YES)
1579: OR (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1580: AND instr(p_event_code, ev.pricing_event_code || ',') > 0
1581: --and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_YES
1582: --or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
1583: AND (line.price_flag = QP_PREQ_PUB.G_YES
1577: AND ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
1578: AND ph.freight_exists = QP_PREQ_PUB.G_YES)
1579: OR (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1580: AND instr(p_event_code, ev.pricing_event_code || ',') > 0
1581: --and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_YES
1582: --or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
1583: AND (line.price_flag = QP_PREQ_PUB.G_YES
1584: OR (line.price_flag = QP_PREQ_PUB.G_PHASE
1585: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) = QP_PREQ_PUB.G_YES))))
1578: AND ph.freight_exists = QP_PREQ_PUB.G_YES)
1579: OR (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1580: AND instr(p_event_code, ev.pricing_event_code || ',') > 0
1581: --and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_YES
1582: --or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
1583: AND (line.price_flag = QP_PREQ_PUB.G_YES
1584: OR (line.price_flag = QP_PREQ_PUB.G_PHASE
1585: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) = QP_PREQ_PUB.G_YES))))
1586: OR adj.pricing_phase_id NOT IN (SELECT ph.pricing_phase_id
1579: OR (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1580: AND instr(p_event_code, ev.pricing_event_code || ',') > 0
1581: --and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_YES
1582: --or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
1583: AND (line.price_flag = QP_PREQ_PUB.G_YES
1584: OR (line.price_flag = QP_PREQ_PUB.G_PHASE
1585: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) = QP_PREQ_PUB.G_YES))))
1586: OR adj.pricing_phase_id NOT IN (SELECT ph.pricing_phase_id
1587: FROM qp_event_phases ev, qp_pricing_phases ph
1580: AND instr(p_event_code, ev.pricing_event_code || ',') > 0
1581: --and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_YES
1582: --or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
1583: AND (line.price_flag = QP_PREQ_PUB.G_YES
1584: OR (line.price_flag = QP_PREQ_PUB.G_PHASE
1585: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) = QP_PREQ_PUB.G_YES))))
1586: OR adj.pricing_phase_id NOT IN (SELECT ph.pricing_phase_id
1587: FROM qp_event_phases ev, qp_pricing_phases ph
1588: WHERE ph.pricing_phase_id = ev.pricing_phase_id
1581: --and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_YES
1582: --or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
1583: AND (line.price_flag = QP_PREQ_PUB.G_YES
1584: OR (line.price_flag = QP_PREQ_PUB.G_PHASE
1585: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) = QP_PREQ_PUB.G_YES))))
1586: OR adj.pricing_phase_id NOT IN (SELECT ph.pricing_phase_id
1587: FROM qp_event_phases ev, qp_pricing_phases ph
1588: WHERE ph.pricing_phase_id = ev.pricing_phase_id
1589: AND ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
1585: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) = QP_PREQ_PUB.G_YES))))
1586: OR adj.pricing_phase_id NOT IN (SELECT ph.pricing_phase_id
1587: FROM qp_event_phases ev, qp_pricing_phases ph
1588: WHERE ph.pricing_phase_id = ev.pricing_phase_id
1589: AND ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
1590: AND ph.freight_exists = QP_PREQ_PUB.G_YES)
1591: OR (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1592: AND instr(p_event_code, ev.pricing_event_code || ',') > 0
1593: --and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index,line.price_flag) = QP_PREQ_PUB.G_YES
1586: OR adj.pricing_phase_id NOT IN (SELECT ph.pricing_phase_id
1587: FROM qp_event_phases ev, qp_pricing_phases ph
1588: WHERE ph.pricing_phase_id = ev.pricing_phase_id
1589: AND ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
1590: AND ph.freight_exists = QP_PREQ_PUB.G_YES)
1591: OR (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1592: AND instr(p_event_code, ev.pricing_event_code || ',') > 0
1593: --and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index,line.price_flag) = QP_PREQ_PUB.G_YES
1594: --or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
1587: FROM qp_event_phases ev, qp_pricing_phases ph
1588: WHERE ph.pricing_phase_id = ev.pricing_phase_id
1589: AND ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
1590: AND ph.freight_exists = QP_PREQ_PUB.G_YES)
1591: OR (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1592: AND instr(p_event_code, ev.pricing_event_code || ',') > 0
1593: --and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index,line.price_flag) = QP_PREQ_PUB.G_YES
1594: --or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
1595: AND (line.price_flag = QP_PREQ_PUB.G_YES
1589: AND ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
1590: AND ph.freight_exists = QP_PREQ_PUB.G_YES)
1591: OR (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1592: AND instr(p_event_code, ev.pricing_event_code || ',') > 0
1593: --and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index,line.price_flag) = QP_PREQ_PUB.G_YES
1594: --or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
1595: AND (line.price_flag = QP_PREQ_PUB.G_YES
1596: OR (line.price_flag = QP_PREQ_PUB.G_PHASE
1597: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) =
1590: AND ph.freight_exists = QP_PREQ_PUB.G_YES)
1591: OR (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1592: AND instr(p_event_code, ev.pricing_event_code || ',') > 0
1593: --and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index,line.price_flag) = QP_PREQ_PUB.G_YES
1594: --or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
1595: AND (line.price_flag = QP_PREQ_PUB.G_YES
1596: OR (line.price_flag = QP_PREQ_PUB.G_PHASE
1597: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) =
1598: QP_PREQ_PUB.G_YES))))
1591: OR (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1592: AND instr(p_event_code, ev.pricing_event_code || ',') > 0
1593: --and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index,line.price_flag) = QP_PREQ_PUB.G_YES
1594: --or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
1595: AND (line.price_flag = QP_PREQ_PUB.G_YES
1596: OR (line.price_flag = QP_PREQ_PUB.G_PHASE
1597: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) =
1598: QP_PREQ_PUB.G_YES))))
1599: AND adj.modifier_level_code IN (QP_PREQ_PUB.G_LINE_LEVEL, QP_PREQ_PUB.G_LINE_GROUP)
1592: AND instr(p_event_code, ev.pricing_event_code || ',') > 0
1593: --and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index,line.price_flag) = QP_PREQ_PUB.G_YES
1594: --or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
1595: AND (line.price_flag = QP_PREQ_PUB.G_YES
1596: OR (line.price_flag = QP_PREQ_PUB.G_PHASE
1597: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) =
1598: QP_PREQ_PUB.G_YES))))
1599: AND adj.modifier_level_code IN (QP_PREQ_PUB.G_LINE_LEVEL, QP_PREQ_PUB.G_LINE_GROUP)
1600: AND qplh.list_header_id = adj.list_header_id
1594: --or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
1595: AND (line.price_flag = QP_PREQ_PUB.G_YES
1596: OR (line.price_flag = QP_PREQ_PUB.G_PHASE
1597: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) =
1598: QP_PREQ_PUB.G_YES))))
1599: AND adj.modifier_level_code IN (QP_PREQ_PUB.G_LINE_LEVEL, QP_PREQ_PUB.G_LINE_GROUP)
1600: AND qplh.list_header_id = adj.list_header_id
1601: AND qplh.list_type_code NOT IN (QP_PREQ_PUB.G_PRICE_LIST_HEADER,
1602: QP_PREQ_PUB.G_AGR_LIST_HEADER)
1595: AND (line.price_flag = QP_PREQ_PUB.G_YES
1596: OR (line.price_flag = QP_PREQ_PUB.G_PHASE
1597: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) =
1598: QP_PREQ_PUB.G_YES))))
1599: AND adj.modifier_level_code IN (QP_PREQ_PUB.G_LINE_LEVEL, QP_PREQ_PUB.G_LINE_GROUP)
1600: AND qplh.list_header_id = adj.list_header_id
1601: AND qplh.list_type_code NOT IN (QP_PREQ_PUB.G_PRICE_LIST_HEADER,
1602: QP_PREQ_PUB.G_AGR_LIST_HEADER)
1603: --commented out because we want to fetch PBH childs and PRG freeline adjs
1597: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) =
1598: QP_PREQ_PUB.G_YES))))
1599: AND adj.modifier_level_code IN (QP_PREQ_PUB.G_LINE_LEVEL, QP_PREQ_PUB.G_LINE_GROUP)
1600: AND qplh.list_header_id = adj.list_header_id
1601: AND qplh.list_type_code NOT IN (QP_PREQ_PUB.G_PRICE_LIST_HEADER,
1602: QP_PREQ_PUB.G_AGR_LIST_HEADER)
1603: --commented out because we want to fetch PBH childs and PRG freeline adjs
1604: --and not exists (select 'x'
1605: -- from oe_price_adj_assocs a
1598: QP_PREQ_PUB.G_YES))))
1599: AND adj.modifier_level_code IN (QP_PREQ_PUB.G_LINE_LEVEL, QP_PREQ_PUB.G_LINE_GROUP)
1600: AND qplh.list_header_id = adj.list_header_id
1601: AND qplh.list_type_code NOT IN (QP_PREQ_PUB.G_PRICE_LIST_HEADER,
1602: QP_PREQ_PUB.G_AGR_LIST_HEADER)
1603: --commented out because we want to fetch PBH childs and PRG freeline adjs
1604: --and not exists (select 'x'
1605: -- from oe_price_adj_assocs a
1606: -- where a.RLTD_PRICE_ADJ_ID = adj.price_adjustment_id)
1656: , NULL PRICING_EFFECTIVE_DATE
1657: , NULL BASE_CURRENCY_CODE
1658: --, line.line_quantity
1659: , adj.range_break_quantity line_quantity
1660: , nvl(adj.updated_flag, QP_PREQ_PUB.G_NO) updated_flag
1661: , NULL calculation_code
1662: , adj.CHANGE_REASON_CODE
1663: , adj.CHANGE_REASON_TEXT
1664: , adj.PRICE_ADJUSTMENT_ID
1670: , NULL BREAK_UOM_ATTRIBUTE
1671: , NULL PROCESS_CODE -- 3215497
1672: FROM qp_int_lines line, oe_price_adjustments adj,
1673: qp_list_headers_b qplh
1674: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
1675: AND line.process_status IN (QP_PREQ_PUB.G_STATUS_UPDATED,
1676: QP_PREQ_PUB.G_STATUS_NEW,
1677: QP_PREQ_PUB.G_STATUS_UNCHANGED,
1678: 'NEW' || QP_PREQ_PUB.G_STATUS_UNCHANGED,
1671: , NULL PROCESS_CODE -- 3215497
1672: FROM qp_int_lines line, oe_price_adjustments adj,
1673: qp_list_headers_b qplh
1674: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
1675: AND line.process_status IN (QP_PREQ_PUB.G_STATUS_UPDATED,
1676: QP_PREQ_PUB.G_STATUS_NEW,
1677: QP_PREQ_PUB.G_STATUS_UNCHANGED,
1678: 'NEW' || QP_PREQ_PUB.G_STATUS_UNCHANGED,
1679: 'OLD' || QP_PREQ_PUB.G_STATUS_UNCHANGED)
1672: FROM qp_int_lines line, oe_price_adjustments adj,
1673: qp_list_headers_b qplh
1674: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
1675: AND line.process_status IN (QP_PREQ_PUB.G_STATUS_UPDATED,
1676: QP_PREQ_PUB.G_STATUS_NEW,
1677: QP_PREQ_PUB.G_STATUS_UNCHANGED,
1678: 'NEW' || QP_PREQ_PUB.G_STATUS_UNCHANGED,
1679: 'OLD' || QP_PREQ_PUB.G_STATUS_UNCHANGED)
1680: AND adj.header_id = line.line_id
1673: qp_list_headers_b qplh
1674: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
1675: AND line.process_status IN (QP_PREQ_PUB.G_STATUS_UPDATED,
1676: QP_PREQ_PUB.G_STATUS_NEW,
1677: QP_PREQ_PUB.G_STATUS_UNCHANGED,
1678: 'NEW' || QP_PREQ_PUB.G_STATUS_UNCHANGED,
1679: 'OLD' || QP_PREQ_PUB.G_STATUS_UNCHANGED)
1680: AND adj.header_id = line.line_id
1681: AND line.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
1674: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
1675: AND line.process_status IN (QP_PREQ_PUB.G_STATUS_UPDATED,
1676: QP_PREQ_PUB.G_STATUS_NEW,
1677: QP_PREQ_PUB.G_STATUS_UNCHANGED,
1678: 'NEW' || QP_PREQ_PUB.G_STATUS_UNCHANGED,
1679: 'OLD' || QP_PREQ_PUB.G_STATUS_UNCHANGED)
1680: AND adj.header_id = line.line_id
1681: AND line.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
1682: , QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
1675: AND line.process_status IN (QP_PREQ_PUB.G_STATUS_UPDATED,
1676: QP_PREQ_PUB.G_STATUS_NEW,
1677: QP_PREQ_PUB.G_STATUS_UNCHANGED,
1678: 'NEW' || QP_PREQ_PUB.G_STATUS_UNCHANGED,
1679: 'OLD' || QP_PREQ_PUB.G_STATUS_UNCHANGED)
1680: AND adj.header_id = line.line_id
1681: AND line.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
1682: , QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
1683: , QP_PREQ_PUB.G_STATUS_UNCHANGED)
1677: QP_PREQ_PUB.G_STATUS_UNCHANGED,
1678: 'NEW' || QP_PREQ_PUB.G_STATUS_UNCHANGED,
1679: 'OLD' || QP_PREQ_PUB.G_STATUS_UNCHANGED)
1680: AND adj.header_id = line.line_id
1681: AND line.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
1682: , QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
1683: , QP_PREQ_PUB.G_STATUS_UNCHANGED)
1684: AND line.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL
1685: AND (line.price_flag IN (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
1678: 'NEW' || QP_PREQ_PUB.G_STATUS_UNCHANGED,
1679: 'OLD' || QP_PREQ_PUB.G_STATUS_UNCHANGED)
1680: AND adj.header_id = line.line_id
1681: AND line.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
1682: , QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
1683: , QP_PREQ_PUB.G_STATUS_UNCHANGED)
1684: AND line.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL
1685: AND (line.price_flag IN (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
1686: OR nvl(line.processed_code, '0') = QP_PREQ_PUB.G_BY_ENGINE)
1679: 'OLD' || QP_PREQ_PUB.G_STATUS_UNCHANGED)
1680: AND adj.header_id = line.line_id
1681: AND line.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
1682: , QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
1683: , QP_PREQ_PUB.G_STATUS_UNCHANGED)
1684: AND line.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL
1685: AND (line.price_flag IN (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
1686: OR nvl(line.processed_code, '0') = QP_PREQ_PUB.G_BY_ENGINE)
1687: AND (adj.updated_flag = QP_PREQ_PUB.G_YES
1680: AND adj.header_id = line.line_id
1681: AND line.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
1682: , QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
1683: , QP_PREQ_PUB.G_STATUS_UNCHANGED)
1684: AND line.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL
1685: AND (line.price_flag IN (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
1686: OR nvl(line.processed_code, '0') = QP_PREQ_PUB.G_BY_ENGINE)
1687: AND (adj.updated_flag = QP_PREQ_PUB.G_YES
1688: OR line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1681: AND line.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
1682: , QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
1683: , QP_PREQ_PUB.G_STATUS_UNCHANGED)
1684: AND line.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL
1685: AND (line.price_flag IN (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
1686: OR nvl(line.processed_code, '0') = QP_PREQ_PUB.G_BY_ENGINE)
1687: AND (adj.updated_flag = QP_PREQ_PUB.G_YES
1688: OR line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1689: OR (p_event_code IS NULL AND adj.updated_flag IS NULL)
1682: , QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
1683: , QP_PREQ_PUB.G_STATUS_UNCHANGED)
1684: AND line.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL
1685: AND (line.price_flag IN (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
1686: OR nvl(line.processed_code, '0') = QP_PREQ_PUB.G_BY_ENGINE)
1687: AND (adj.updated_flag = QP_PREQ_PUB.G_YES
1688: OR line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1689: OR (p_event_code IS NULL AND adj.updated_flag IS NULL)
1690: OR adj.pricing_phase_id NOT IN
1683: , QP_PREQ_PUB.G_STATUS_UNCHANGED)
1684: AND line.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL
1685: AND (line.price_flag IN (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
1686: OR nvl(line.processed_code, '0') = QP_PREQ_PUB.G_BY_ENGINE)
1687: AND (adj.updated_flag = QP_PREQ_PUB.G_YES
1688: OR line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1689: OR (p_event_code IS NULL AND adj.updated_flag IS NULL)
1690: OR adj.pricing_phase_id NOT IN
1691: (SELECT ph.pricing_phase_id
1684: AND line.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL
1685: AND (line.price_flag IN (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
1686: OR nvl(line.processed_code, '0') = QP_PREQ_PUB.G_BY_ENGINE)
1687: AND (adj.updated_flag = QP_PREQ_PUB.G_YES
1688: OR line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1689: OR (p_event_code IS NULL AND adj.updated_flag IS NULL)
1690: OR adj.pricing_phase_id NOT IN
1691: (SELECT ph.pricing_phase_id
1692: FROM qp_event_phases ev, qp_pricing_phases ph
1690: OR adj.pricing_phase_id NOT IN
1691: (SELECT ph.pricing_phase_id
1692: FROM qp_event_phases ev, qp_pricing_phases ph
1693: WHERE ph.pricing_phase_id = ev.pricing_phase_id
1694: AND ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
1695: AND ph.freight_exists = QP_PREQ_PUB.G_YES)
1696: OR (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1697: AND instr(p_event_code, ev.pricing_event_code || ',') > 0
1698: --and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_YES
1691: (SELECT ph.pricing_phase_id
1692: FROM qp_event_phases ev, qp_pricing_phases ph
1693: WHERE ph.pricing_phase_id = ev.pricing_phase_id
1694: AND ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
1695: AND ph.freight_exists = QP_PREQ_PUB.G_YES)
1696: OR (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1697: AND instr(p_event_code, ev.pricing_event_code || ',') > 0
1698: --and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_YES
1699: --or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
1692: FROM qp_event_phases ev, qp_pricing_phases ph
1693: WHERE ph.pricing_phase_id = ev.pricing_phase_id
1694: AND ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
1695: AND ph.freight_exists = QP_PREQ_PUB.G_YES)
1696: OR (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1697: AND instr(p_event_code, ev.pricing_event_code || ',') > 0
1698: --and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_YES
1699: --or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
1700: AND (line.price_flag = QP_PREQ_PUB.G_YES
1694: AND ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
1695: AND ph.freight_exists = QP_PREQ_PUB.G_YES)
1696: OR (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1697: AND instr(p_event_code, ev.pricing_event_code || ',') > 0
1698: --and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_YES
1699: --or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
1700: AND (line.price_flag = QP_PREQ_PUB.G_YES
1701: OR (line.price_flag = QP_PREQ_PUB.G_PHASE
1702: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) = QP_PREQ_PUB.G_YES))))
1695: AND ph.freight_exists = QP_PREQ_PUB.G_YES)
1696: OR (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1697: AND instr(p_event_code, ev.pricing_event_code || ',') > 0
1698: --and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_YES
1699: --or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
1700: AND (line.price_flag = QP_PREQ_PUB.G_YES
1701: OR (line.price_flag = QP_PREQ_PUB.G_PHASE
1702: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) = QP_PREQ_PUB.G_YES))))
1703: AND adj.modifier_level_code = QP_PREQ_PUB.G_ORDER_LEVEL
1696: OR (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1697: AND instr(p_event_code, ev.pricing_event_code || ',') > 0
1698: --and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_YES
1699: --or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
1700: AND (line.price_flag = QP_PREQ_PUB.G_YES
1701: OR (line.price_flag = QP_PREQ_PUB.G_PHASE
1702: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) = QP_PREQ_PUB.G_YES))))
1703: AND adj.modifier_level_code = QP_PREQ_PUB.G_ORDER_LEVEL
1704: AND adj.line_id IS NULL
1697: AND instr(p_event_code, ev.pricing_event_code || ',') > 0
1698: --and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_YES
1699: --or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
1700: AND (line.price_flag = QP_PREQ_PUB.G_YES
1701: OR (line.price_flag = QP_PREQ_PUB.G_PHASE
1702: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) = QP_PREQ_PUB.G_YES))))
1703: AND adj.modifier_level_code = QP_PREQ_PUB.G_ORDER_LEVEL
1704: AND adj.line_id IS NULL
1705: AND qplh.list_header_id = adj.list_header_id
1698: --and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_YES
1699: --or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
1700: AND (line.price_flag = QP_PREQ_PUB.G_YES
1701: OR (line.price_flag = QP_PREQ_PUB.G_PHASE
1702: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) = QP_PREQ_PUB.G_YES))))
1703: AND adj.modifier_level_code = QP_PREQ_PUB.G_ORDER_LEVEL
1704: AND adj.line_id IS NULL
1705: AND qplh.list_header_id = adj.list_header_id
1706: AND qplh.list_type_code NOT IN (QP_PREQ_PUB.G_PRICE_LIST_HEADER, QP_PREQ_PUB.G_AGR_LIST_HEADER);
1699: --or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
1700: AND (line.price_flag = QP_PREQ_PUB.G_YES
1701: OR (line.price_flag = QP_PREQ_PUB.G_PHASE
1702: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) = QP_PREQ_PUB.G_YES))))
1703: AND adj.modifier_level_code = QP_PREQ_PUB.G_ORDER_LEVEL
1704: AND adj.line_id IS NULL
1705: AND qplh.list_header_id = adj.list_header_id
1706: AND qplh.list_type_code NOT IN (QP_PREQ_PUB.G_PRICE_LIST_HEADER, QP_PREQ_PUB.G_AGR_LIST_HEADER);
1707:
1702: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) = QP_PREQ_PUB.G_YES))))
1703: AND adj.modifier_level_code = QP_PREQ_PUB.G_ORDER_LEVEL
1704: AND adj.line_id IS NULL
1705: AND qplh.list_header_id = adj.list_header_id
1706: AND qplh.list_type_code NOT IN (QP_PREQ_PUB.G_PRICE_LIST_HEADER, QP_PREQ_PUB.G_AGR_LIST_HEADER);
1707:
1708: CURSOR l_rltd_lines_cur(pbh_exist_flag VARCHAR2, prg_exist_flag VARCHAR2) IS
1709: SELECT /*+ index(ass OE_PRICE_ADJ_ASSOCS_N1)*/ --[julin/4865213] changed from N3 to N1
1710: line.line_index line_index,
1708: CURSOR l_rltd_lines_cur(pbh_exist_flag VARCHAR2, prg_exist_flag VARCHAR2) IS
1709: SELECT /*+ index(ass OE_PRICE_ADJ_ASSOCS_N1)*/ --[julin/4865213] changed from N3 to N1
1710: line.line_index line_index,
1711: ass.price_adjustment_id line_detail_index,
1712: QP_PREQ_PUB.G_PBH_LINE relationship_type_code,
1713: line.line_index related_line_index,
1714: ass.rltd_price_adj_id related_line_detail_index,
1715: adj_pbh.list_line_id list_line_id,
1716: adj.list_line_id related_list_line_id,
1720: , oe_price_adj_assocs ass
1721: , oe_price_adjustments adj
1722: , qp_pricing_attributes attr
1723: WHERE pbh_exist_flag = 'Y'
1724: AND QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
1725: AND line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
1726: AND line.price_flag IN (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE)
1727: AND line.line_id = adj_pbh.line_id
1728: AND adj_pbh.list_line_type_code = QP_PREQ_PUB.G_PRICE_BREAK_TYPE
1721: , oe_price_adjustments adj
1722: , qp_pricing_attributes attr
1723: WHERE pbh_exist_flag = 'Y'
1724: AND QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
1725: AND line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
1726: AND line.price_flag IN (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE)
1727: AND line.line_id = adj_pbh.line_id
1728: AND adj_pbh.list_line_type_code = QP_PREQ_PUB.G_PRICE_BREAK_TYPE
1729: AND (p_calculate_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1722: , qp_pricing_attributes attr
1723: WHERE pbh_exist_flag = 'Y'
1724: AND QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
1725: AND line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
1726: AND line.price_flag IN (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE)
1727: AND line.line_id = adj_pbh.line_id
1728: AND adj_pbh.list_line_type_code = QP_PREQ_PUB.G_PRICE_BREAK_TYPE
1729: AND (p_calculate_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1730: OR line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1724: AND QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
1725: AND line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
1726: AND line.price_flag IN (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE)
1727: AND line.line_id = adj_pbh.line_id
1728: AND adj_pbh.list_line_type_code = QP_PREQ_PUB.G_PRICE_BREAK_TYPE
1729: AND (p_calculate_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1730: OR line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1731: OR adj_pbh.updated_flag = QP_PREQ_PUB.G_YES
1732: OR p_event_code = ',' --we pad comma when it is null
1725: AND line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
1726: AND line.price_flag IN (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE)
1727: AND line.line_id = adj_pbh.line_id
1728: AND adj_pbh.list_line_type_code = QP_PREQ_PUB.G_PRICE_BREAK_TYPE
1729: AND (p_calculate_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1730: OR line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1731: OR adj_pbh.updated_flag = QP_PREQ_PUB.G_YES
1732: OR p_event_code = ',' --we pad comma when it is null
1733: OR adj_pbh.pricing_phase_id NOT IN
1726: AND line.price_flag IN (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE)
1727: AND line.line_id = adj_pbh.line_id
1728: AND adj_pbh.list_line_type_code = QP_PREQ_PUB.G_PRICE_BREAK_TYPE
1729: AND (p_calculate_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1730: OR line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1731: OR adj_pbh.updated_flag = QP_PREQ_PUB.G_YES
1732: OR p_event_code = ',' --we pad comma when it is null
1733: OR adj_pbh.pricing_phase_id NOT IN
1734: (SELECT ph.pricing_phase_id
1727: AND line.line_id = adj_pbh.line_id
1728: AND adj_pbh.list_line_type_code = QP_PREQ_PUB.G_PRICE_BREAK_TYPE
1729: AND (p_calculate_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1730: OR line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1731: OR adj_pbh.updated_flag = QP_PREQ_PUB.G_YES
1732: OR p_event_code = ',' --we pad comma when it is null
1733: OR adj_pbh.pricing_phase_id NOT IN
1734: (SELECT ph.pricing_phase_id
1735: FROM qp_event_phases evt, qp_pricing_phases ph
1735: FROM qp_event_phases evt, qp_pricing_phases ph
1736: WHERE ph.pricing_phase_id = evt.pricing_phase_id
1737: --introduced for freight_rating functionality to return only modifiers in
1738: --phases where freight_exist = 'Y' if G_GET_FREIGHT_FLAG = 'Y'
1739: AND ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
1740: AND ph.freight_exists = QP_PREQ_PUB.G_YES)
1741: OR (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1742: AND instr(p_event_code, evt.pricing_event_code || ',') > 0
1743: --and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_YES
1736: WHERE ph.pricing_phase_id = evt.pricing_phase_id
1737: --introduced for freight_rating functionality to return only modifiers in
1738: --phases where freight_exist = 'Y' if G_GET_FREIGHT_FLAG = 'Y'
1739: AND ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
1740: AND ph.freight_exists = QP_PREQ_PUB.G_YES)
1741: OR (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1742: AND instr(p_event_code, evt.pricing_event_code || ',') > 0
1743: --and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_YES
1744: AND (line.price_flag = QP_PREQ_PUB.G_YES
1737: --introduced for freight_rating functionality to return only modifiers in
1738: --phases where freight_exist = 'Y' if G_GET_FREIGHT_FLAG = 'Y'
1739: AND ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
1740: AND ph.freight_exists = QP_PREQ_PUB.G_YES)
1741: OR (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1742: AND instr(p_event_code, evt.pricing_event_code || ',') > 0
1743: --and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_YES
1744: AND (line.price_flag = QP_PREQ_PUB.G_YES
1745: --or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
1739: AND ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
1740: AND ph.freight_exists = QP_PREQ_PUB.G_YES)
1741: OR (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1742: AND instr(p_event_code, evt.pricing_event_code || ',') > 0
1743: --and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_YES
1744: AND (line.price_flag = QP_PREQ_PUB.G_YES
1745: --or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
1746: OR (line.price_flag = QP_PREQ_PUB.G_PHASE
1747: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) =
1740: AND ph.freight_exists = QP_PREQ_PUB.G_YES)
1741: OR (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1742: AND instr(p_event_code, evt.pricing_event_code || ',') > 0
1743: --and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_YES
1744: AND (line.price_flag = QP_PREQ_PUB.G_YES
1745: --or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
1746: OR (line.price_flag = QP_PREQ_PUB.G_PHASE
1747: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) =
1748: QP_PREQ_PUB.G_YES))))
1741: OR (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1742: AND instr(p_event_code, evt.pricing_event_code || ',') > 0
1743: --and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_YES
1744: AND (line.price_flag = QP_PREQ_PUB.G_YES
1745: --or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
1746: OR (line.price_flag = QP_PREQ_PUB.G_PHASE
1747: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) =
1748: QP_PREQ_PUB.G_YES))))
1749: AND adj_pbh.price_adjustment_id = ass.price_adjustment_id
1742: AND instr(p_event_code, evt.pricing_event_code || ',') > 0
1743: --and (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_YES
1744: AND (line.price_flag = QP_PREQ_PUB.G_YES
1745: --or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
1746: OR (line.price_flag = QP_PREQ_PUB.G_PHASE
1747: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) =
1748: QP_PREQ_PUB.G_YES))))
1749: AND adj_pbh.price_adjustment_id = ass.price_adjustment_id
1750: AND ass.rltd_price_adj_id = adj.price_adjustment_id
1744: AND (line.price_flag = QP_PREQ_PUB.G_YES
1745: --or (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.line_detail_index),line.price_flag) = QP_PREQ_PUB.G_PHASE
1746: OR (line.price_flag = QP_PREQ_PUB.G_PHASE
1747: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) =
1748: QP_PREQ_PUB.G_YES))))
1749: AND adj_pbh.price_adjustment_id = ass.price_adjustment_id
1750: AND ass.rltd_price_adj_id = adj.price_adjustment_id
1751: AND attr.list_line_id = adj.list_line_id
1752: AND attr.pricing_attribute_context = QP_PREQ_PUB.G_PRIC_VOLUME_CONTEXT
1748: QP_PREQ_PUB.G_YES))))
1749: AND adj_pbh.price_adjustment_id = ass.price_adjustment_id
1750: AND ass.rltd_price_adj_id = adj.price_adjustment_id
1751: AND attr.list_line_id = adj.list_line_id
1752: AND attr.pricing_attribute_context = QP_PREQ_PUB.G_PRIC_VOLUME_CONTEXT
1753: UNION ALL
1754: SELECT /*+ ordered index(ass OE_PRICE_ADJ_ASSOCS_N1)*/ --[julin/4865213] changed from N3 to N1, ordered
1755: line.line_index line_index,
1756: adj_prg.price_adjustment_id line_detail_index,
1753: UNION ALL
1754: SELECT /*+ ordered index(ass OE_PRICE_ADJ_ASSOCS_N1)*/ --[julin/4865213] changed from N3 to N1, ordered
1755: line.line_index line_index,
1756: adj_prg.price_adjustment_id line_detail_index,
1757: QP_PREQ_PUB.G_GENERATED_LINE relationship_type_code,
1758: getline.line_index related_line_index,
1759: ass.rltd_price_adj_id related_line_detail_index,
1760: adj_prg.list_line_id list_line_id,
1761: adj.list_line_id related_list_line_id,
1765: , oe_price_adj_assocs ass
1766: , oe_price_adjustments adj
1767: , qp_int_lines getline
1768: WHERE prg_exist_flag = 'Y'
1769: AND QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
1770: AND line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
1771: --and line.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE)
1772: AND line.price_flag = G_YES
1773: AND line.line_id = adj_prg.line_id
1766: , oe_price_adjustments adj
1767: , qp_int_lines getline
1768: WHERE prg_exist_flag = 'Y'
1769: AND QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
1770: AND line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
1771: --and line.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE)
1772: AND line.price_flag = G_YES
1773: AND line.line_id = adj_prg.line_id
1774: AND adj_prg.list_line_type_code = G_PROMO_GOODS_DISCOUNT
1767: , qp_int_lines getline
1768: WHERE prg_exist_flag = 'Y'
1769: AND QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
1770: AND line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
1771: --and line.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE)
1772: AND line.price_flag = G_YES
1773: AND line.line_id = adj_prg.line_id
1774: AND adj_prg.list_line_type_code = G_PROMO_GOODS_DISCOUNT
1775: AND adj_prg.pricing_phase_id IN
1776: (SELECT ph.pricing_phase_id
1777: FROM qp_event_phases evt, qp_pricing_phases ph
1778: WHERE ph.pricing_phase_id = evt.pricing_phase_id
1779: AND instr(p_event_code, evt.pricing_event_code || ',') > 0
1780: AND (line.price_flag = QP_PREQ_PUB.G_YES
1781: OR (line.price_flag = QP_PREQ_PUB.G_PHASE
1782: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) = QP_PREQ_PUB.G_YES)))
1783: AND ass.price_adjustment_id = adj_prg.price_adjustment_id
1784: AND ass.rltd_price_adj_id = adj.price_adjustment_id
1777: FROM qp_event_phases evt, qp_pricing_phases ph
1778: WHERE ph.pricing_phase_id = evt.pricing_phase_id
1779: AND instr(p_event_code, evt.pricing_event_code || ',') > 0
1780: AND (line.price_flag = QP_PREQ_PUB.G_YES
1781: OR (line.price_flag = QP_PREQ_PUB.G_PHASE
1782: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) = QP_PREQ_PUB.G_YES)))
1783: AND ass.price_adjustment_id = adj_prg.price_adjustment_id
1784: AND ass.rltd_price_adj_id = adj.price_adjustment_id
1785: AND adj.list_line_type_code = 'DIS'
1778: WHERE ph.pricing_phase_id = evt.pricing_phase_id
1779: AND instr(p_event_code, evt.pricing_event_code || ',') > 0
1780: AND (line.price_flag = QP_PREQ_PUB.G_YES
1781: OR (line.price_flag = QP_PREQ_PUB.G_PHASE
1782: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) = QP_PREQ_PUB.G_YES)))
1783: AND ass.price_adjustment_id = adj_prg.price_adjustment_id
1784: AND ass.rltd_price_adj_id = adj.price_adjustment_id
1785: AND adj.list_line_type_code = 'DIS'
1786: AND adj.line_id IS NOT NULL
1784: AND ass.rltd_price_adj_id = adj.price_adjustment_id
1785: AND adj.list_line_type_code = 'DIS'
1786: AND adj.line_id IS NOT NULL
1787: AND getline.line_id = adj.line_id;
1788: --and getline.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE);
1789:
1790: CURSOR l_pbh_adj_exists_cur IS
1791: SELECT /*+ index(adj OE_PRICE_ADJUSTMENTS_N2) */ 'Y'
1792: FROM
1792: FROM
1793: qp_int_lines line,
1794: oe_price_adjustments adj
1795: WHERE adj.line_id = line.line_id
1796: AND line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
1797: AND adj.list_line_type_code = QP_PREQ_PUB.G_PRICE_BREAK_TYPE
1798: AND (p_calculate_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1799: OR line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1800: OR adj.updated_flag = QP_PREQ_PUB.G_YES
1793: qp_int_lines line,
1794: oe_price_adjustments adj
1795: WHERE adj.line_id = line.line_id
1796: AND line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
1797: AND adj.list_line_type_code = QP_PREQ_PUB.G_PRICE_BREAK_TYPE
1798: AND (p_calculate_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1799: OR line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1800: OR adj.updated_flag = QP_PREQ_PUB.G_YES
1801: OR p_event_code = ',' -- we pad ',' when it is null
1794: oe_price_adjustments adj
1795: WHERE adj.line_id = line.line_id
1796: AND line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
1797: AND adj.list_line_type_code = QP_PREQ_PUB.G_PRICE_BREAK_TYPE
1798: AND (p_calculate_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1799: OR line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1800: OR adj.updated_flag = QP_PREQ_PUB.G_YES
1801: OR p_event_code = ',' -- we pad ',' when it is null
1802: OR adj.pricing_phase_id NOT IN
1795: WHERE adj.line_id = line.line_id
1796: AND line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
1797: AND adj.list_line_type_code = QP_PREQ_PUB.G_PRICE_BREAK_TYPE
1798: AND (p_calculate_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1799: OR line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1800: OR adj.updated_flag = QP_PREQ_PUB.G_YES
1801: OR p_event_code = ',' -- we pad ',' when it is null
1802: OR adj.pricing_phase_id NOT IN
1803: (SELECT ph.pricing_phase_id
1796: AND line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
1797: AND adj.list_line_type_code = QP_PREQ_PUB.G_PRICE_BREAK_TYPE
1798: AND (p_calculate_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1799: OR line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1800: OR adj.updated_flag = QP_PREQ_PUB.G_YES
1801: OR p_event_code = ',' -- we pad ',' when it is null
1802: OR adj.pricing_phase_id NOT IN
1803: (SELECT ph.pricing_phase_id
1804: FROM qp_event_phases evt, qp_pricing_phases ph
1804: FROM qp_event_phases evt, qp_pricing_phases ph
1805: WHERE ph.pricing_phase_id = evt.pricing_phase_id
1806: --introduced for freight_rating functionality to return only modifiers in
1807: --phases where freight_exist = 'Y' if G_GET_FREIGHT_FLAG = 'Y'
1808: AND ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
1809: AND ph.freight_exists = QP_PREQ_PUB.G_YES)
1810: OR (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1811: AND instr(p_event_code, evt.pricing_event_code || ',') > 0
1812: AND (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index), line.price_flag) = QP_PREQ_PUB.G_YES
1805: WHERE ph.pricing_phase_id = evt.pricing_phase_id
1806: --introduced for freight_rating functionality to return only modifiers in
1807: --phases where freight_exist = 'Y' if G_GET_FREIGHT_FLAG = 'Y'
1808: AND ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
1809: AND ph.freight_exists = QP_PREQ_PUB.G_YES)
1810: OR (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1811: AND instr(p_event_code, evt.pricing_event_code || ',') > 0
1812: AND (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index), line.price_flag) = QP_PREQ_PUB.G_YES
1813: OR (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index), line.price_flag) = QP_PREQ_PUB.G_PHASE
1806: --introduced for freight_rating functionality to return only modifiers in
1807: --phases where freight_exist = 'Y' if G_GET_FREIGHT_FLAG = 'Y'
1808: AND ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
1809: AND ph.freight_exists = QP_PREQ_PUB.G_YES)
1810: OR (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1811: AND instr(p_event_code, evt.pricing_event_code || ',') > 0
1812: AND (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index), line.price_flag) = QP_PREQ_PUB.G_YES
1813: OR (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index), line.price_flag) = QP_PREQ_PUB.G_PHASE
1814: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) =
1808: AND ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
1809: AND ph.freight_exists = QP_PREQ_PUB.G_YES)
1810: OR (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1811: AND instr(p_event_code, evt.pricing_event_code || ',') > 0
1812: AND (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index), line.price_flag) = QP_PREQ_PUB.G_YES
1813: OR (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index), line.price_flag) = QP_PREQ_PUB.G_PHASE
1814: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) =
1815: QP_PREQ_PUB.G_YES))))
1816: UNION
1809: AND ph.freight_exists = QP_PREQ_PUB.G_YES)
1810: OR (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1811: AND instr(p_event_code, evt.pricing_event_code || ',') > 0
1812: AND (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index), line.price_flag) = QP_PREQ_PUB.G_YES
1813: OR (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index), line.price_flag) = QP_PREQ_PUB.G_PHASE
1814: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) =
1815: QP_PREQ_PUB.G_YES))))
1816: UNION
1817: SELECT /*+ index(adj OE_PRICE_ADJUSTMENTS_N1) */ 'Y'
1811: AND instr(p_event_code, evt.pricing_event_code || ',') > 0
1812: AND (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index), line.price_flag) = QP_PREQ_PUB.G_YES
1813: OR (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index), line.price_flag) = QP_PREQ_PUB.G_PHASE
1814: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) =
1815: QP_PREQ_PUB.G_YES))))
1816: UNION
1817: SELECT /*+ index(adj OE_PRICE_ADJUSTMENTS_N1) */ 'Y'
1818: FROM
1819: qp_int_lines line,
1818: FROM
1819: qp_int_lines line,
1820: oe_price_adjustments adj
1821: WHERE adj.header_id = line.line_id
1822: AND line.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL
1823: AND adj.list_line_type_code = QP_PREQ_PUB.G_PRICE_BREAK_TYPE
1824: AND (p_calculate_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1825: OR line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1826: OR p_event_code = ',' -- we pad ',' when it is null
1819: qp_int_lines line,
1820: oe_price_adjustments adj
1821: WHERE adj.header_id = line.line_id
1822: AND line.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL
1823: AND adj.list_line_type_code = QP_PREQ_PUB.G_PRICE_BREAK_TYPE
1824: AND (p_calculate_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1825: OR line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1826: OR p_event_code = ',' -- we pad ',' when it is null
1827: OR adj.pricing_phase_id NOT IN
1820: oe_price_adjustments adj
1821: WHERE adj.header_id = line.line_id
1822: AND line.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL
1823: AND adj.list_line_type_code = QP_PREQ_PUB.G_PRICE_BREAK_TYPE
1824: AND (p_calculate_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1825: OR line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1826: OR p_event_code = ',' -- we pad ',' when it is null
1827: OR adj.pricing_phase_id NOT IN
1828: (SELECT ph.pricing_phase_id
1821: WHERE adj.header_id = line.line_id
1822: AND line.line_type_code = QP_PREQ_PUB.G_ORDER_LEVEL
1823: AND adj.list_line_type_code = QP_PREQ_PUB.G_PRICE_BREAK_TYPE
1824: AND (p_calculate_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1825: OR line.price_flag = QP_PREQ_PUB.G_CALCULATE_ONLY
1826: OR p_event_code = ',' -- we pad ',' when it is null
1827: OR adj.pricing_phase_id NOT IN
1828: (SELECT ph.pricing_phase_id
1829: FROM qp_event_phases evt, qp_pricing_phases ph
1829: FROM qp_event_phases evt, qp_pricing_phases ph
1830: WHERE ph.pricing_phase_id = evt.pricing_phase_id
1831: --introduced for freight_rating functionality to return only modifiers in
1832: --phases where freight_exist = 'Y' if G_GET_FREIGHT_FLAG = 'Y'
1833: AND ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
1834: AND ph.freight_exists = QP_PREQ_PUB.G_YES)
1835: OR (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1836: AND instr(p_event_code, evt.pricing_event_code || ',') > 0
1837: AND (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index), line.price_flag) = QP_PREQ_PUB.G_YES
1830: WHERE ph.pricing_phase_id = evt.pricing_phase_id
1831: --introduced for freight_rating functionality to return only modifiers in
1832: --phases where freight_exist = 'Y' if G_GET_FREIGHT_FLAG = 'Y'
1833: AND ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
1834: AND ph.freight_exists = QP_PREQ_PUB.G_YES)
1835: OR (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1836: AND instr(p_event_code, evt.pricing_event_code || ',') > 0
1837: AND (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index), line.price_flag) = QP_PREQ_PUB.G_YES
1838: OR (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index), line.price_flag) = QP_PREQ_PUB.G_PHASE
1831: --introduced for freight_rating functionality to return only modifiers in
1832: --phases where freight_exist = 'Y' if G_GET_FREIGHT_FLAG = 'Y'
1833: AND ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
1834: AND ph.freight_exists = QP_PREQ_PUB.G_YES)
1835: OR (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1836: AND instr(p_event_code, evt.pricing_event_code || ',') > 0
1837: AND (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index), line.price_flag) = QP_PREQ_PUB.G_YES
1838: OR (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index), line.price_flag) = QP_PREQ_PUB.G_PHASE
1839: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) =
1833: AND ((QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_YES
1834: AND ph.freight_exists = QP_PREQ_PUB.G_YES)
1835: OR (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1836: AND instr(p_event_code, evt.pricing_event_code || ',') > 0
1837: AND (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index), line.price_flag) = QP_PREQ_PUB.G_YES
1838: OR (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index), line.price_flag) = QP_PREQ_PUB.G_PHASE
1839: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) =
1840: QP_PREQ_PUB.G_YES))));
1841:
1834: AND ph.freight_exists = QP_PREQ_PUB.G_YES)
1835: OR (QP_PREQ_PUB.G_GET_FREIGHT_FLAG = QP_PREQ_PUB.G_NO))
1836: AND instr(p_event_code, evt.pricing_event_code || ',') > 0
1837: AND (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index), line.price_flag) = QP_PREQ_PUB.G_YES
1838: OR (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index), line.price_flag) = QP_PREQ_PUB.G_PHASE
1839: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) =
1840: QP_PREQ_PUB.G_YES))));
1841:
1842:
1836: AND instr(p_event_code, evt.pricing_event_code || ',') > 0
1837: AND (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index), line.price_flag) = QP_PREQ_PUB.G_YES
1838: OR (nvl(QP_PREQ_PUB.Get_buy_line_price_flag(adj.list_line_id, line.line_index), line.price_flag) = QP_PREQ_PUB.G_PHASE
1839: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) =
1840: QP_PREQ_PUB.G_YES))));
1841:
1842:
1843: CURSOR l_prg_adj_exists_cur IS
1844: SELECT /*+ index(adj OE_PRICE_ADJUSTMENTS_N2) */ 'Y'
1843: CURSOR l_prg_adj_exists_cur IS
1844: SELECT /*+ index(adj OE_PRICE_ADJUSTMENTS_N2) */ 'Y'
1845: FROM qp_int_lines line,
1846: oe_price_adjustments adj
1847: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
1848: AND adj.line_id = line.line_id
1849: AND line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
1850: AND adj.list_line_type_code = 'PRG'
1851: AND adj.pricing_phase_id IN
1845: FROM qp_int_lines line,
1846: oe_price_adjustments adj
1847: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
1848: AND adj.line_id = line.line_id
1849: AND line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
1850: AND adj.list_line_type_code = 'PRG'
1851: AND adj.pricing_phase_id IN
1852: (SELECT ph.pricing_phase_id
1853: FROM qp_event_phases evt, qp_pricing_phases ph
1852: (SELECT ph.pricing_phase_id
1853: FROM qp_event_phases evt, qp_pricing_phases ph
1854: WHERE ph.pricing_phase_id = evt.pricing_phase_id
1855: AND instr(p_event_code, evt.pricing_event_code || ',') > 0
1856: AND (line.price_flag = QP_PREQ_PUB.G_YES
1857: OR (line.price_flag = QP_PREQ_PUB.G_PHASE
1858: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) = QP_PREQ_PUB.G_YES)));
1859:
1860: CURSOR l_getline_dis_cur IS
1853: FROM qp_event_phases evt, qp_pricing_phases ph
1854: WHERE ph.pricing_phase_id = evt.pricing_phase_id
1855: AND instr(p_event_code, evt.pricing_event_code || ',') > 0
1856: AND (line.price_flag = QP_PREQ_PUB.G_YES
1857: OR (line.price_flag = QP_PREQ_PUB.G_PHASE
1858: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) = QP_PREQ_PUB.G_YES)));
1859:
1860: CURSOR l_getline_dis_cur IS
1861: SELECT /*+ ORDERED USE_NL(adj qplh) */
1854: WHERE ph.pricing_phase_id = evt.pricing_phase_id
1855: AND instr(p_event_code, evt.pricing_event_code || ',') > 0
1856: AND (line.price_flag = QP_PREQ_PUB.G_YES
1857: OR (line.price_flag = QP_PREQ_PUB.G_PHASE
1858: AND nvl(ph.user_freeze_override_flag, ph.freeze_override_flag) = QP_PREQ_PUB.G_YES)));
1859:
1860: CURSOR l_getline_dis_cur IS
1861: SELECT /*+ ORDERED USE_NL(adj qplh) */
1862: adj.price_adjustment_id line_detail_index
1909: , NULL PRICING_EFFECTIVE_DATE
1910: , NULL BASE_CURRENCY_CODE
1911: --, line.line_quantity
1912: , adj.range_break_quantity line_quantity
1913: , nvl(adj.updated_flag, QP_PREQ_PUB.G_NO) updated_flag
1914: , NULL calculation_code
1915: , adj.CHANGE_REASON_CODE
1916: , adj.CHANGE_REASON_TEXT
1917: , adj.PRICE_ADJUSTMENT_ID
1923: , NULL BREAK_UOM_ATTRIBUTE
1924: , NULL PROCESS_CODE -- 3215497
1925: FROM qp_int_lines getline, oe_price_adjustments adj,
1926: qp_int_ldets ldet, oe_price_adj_assocs ass
1927: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES
1928: AND ldet.CREATED_FROM_LIST_LINE_TYPE = 'PRG'
1929: AND ass.price_adjustment_id = ldet.price_adjustment_id
1930: AND ass.rltd_price_adj_id = adj.price_adjustment_id
1931: AND adj.line_id = getline.line_id
1929: AND ass.price_adjustment_id = ldet.price_adjustment_id
1930: AND ass.rltd_price_adj_id = adj.price_adjustment_id
1931: AND adj.line_id = getline.line_id
1932: AND getline.line_index = ldet.line_index -- sql repos
1933: AND getline.process_status IN (QP_PREQ_PUB.G_STATUS_UPDATED,
1934: QP_PREQ_PUB.G_STATUS_NEW,
1935: QP_PREQ_PUB.G_STATUS_UNCHANGED,
1936: 'NEW' || QP_PREQ_PUB.G_STATUS_UNCHANGED,
1937: 'OLD' || QP_PREQ_PUB.G_STATUS_UNCHANGED)
1930: AND ass.rltd_price_adj_id = adj.price_adjustment_id
1931: AND adj.line_id = getline.line_id
1932: AND getline.line_index = ldet.line_index -- sql repos
1933: AND getline.process_status IN (QP_PREQ_PUB.G_STATUS_UPDATED,
1934: QP_PREQ_PUB.G_STATUS_NEW,
1935: QP_PREQ_PUB.G_STATUS_UNCHANGED,
1936: 'NEW' || QP_PREQ_PUB.G_STATUS_UNCHANGED,
1937: 'OLD' || QP_PREQ_PUB.G_STATUS_UNCHANGED)
1938: AND getline.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
1931: AND adj.line_id = getline.line_id
1932: AND getline.line_index = ldet.line_index -- sql repos
1933: AND getline.process_status IN (QP_PREQ_PUB.G_STATUS_UPDATED,
1934: QP_PREQ_PUB.G_STATUS_NEW,
1935: QP_PREQ_PUB.G_STATUS_UNCHANGED,
1936: 'NEW' || QP_PREQ_PUB.G_STATUS_UNCHANGED,
1937: 'OLD' || QP_PREQ_PUB.G_STATUS_UNCHANGED)
1938: AND getline.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
1939: , QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
1932: AND getline.line_index = ldet.line_index -- sql repos
1933: AND getline.process_status IN (QP_PREQ_PUB.G_STATUS_UPDATED,
1934: QP_PREQ_PUB.G_STATUS_NEW,
1935: QP_PREQ_PUB.G_STATUS_UNCHANGED,
1936: 'NEW' || QP_PREQ_PUB.G_STATUS_UNCHANGED,
1937: 'OLD' || QP_PREQ_PUB.G_STATUS_UNCHANGED)
1938: AND getline.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
1939: , QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
1940: , QP_PREQ_PUB.G_STATUS_UNCHANGED)
1933: AND getline.process_status IN (QP_PREQ_PUB.G_STATUS_UPDATED,
1934: QP_PREQ_PUB.G_STATUS_NEW,
1935: QP_PREQ_PUB.G_STATUS_UNCHANGED,
1936: 'NEW' || QP_PREQ_PUB.G_STATUS_UNCHANGED,
1937: 'OLD' || QP_PREQ_PUB.G_STATUS_UNCHANGED)
1938: AND getline.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
1939: , QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
1940: , QP_PREQ_PUB.G_STATUS_UNCHANGED)
1941: AND getline.price_flag = QP_PREQ_PUB.G_NO
1934: QP_PREQ_PUB.G_STATUS_NEW,
1935: QP_PREQ_PUB.G_STATUS_UNCHANGED,
1936: 'NEW' || QP_PREQ_PUB.G_STATUS_UNCHANGED,
1937: 'OLD' || QP_PREQ_PUB.G_STATUS_UNCHANGED)
1938: AND getline.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
1939: , QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
1940: , QP_PREQ_PUB.G_STATUS_UNCHANGED)
1941: AND getline.price_flag = QP_PREQ_PUB.G_NO
1942: AND adj.price_adjustment_id NOT IN (SELECT price_adjustment_id
1935: QP_PREQ_PUB.G_STATUS_UNCHANGED,
1936: 'NEW' || QP_PREQ_PUB.G_STATUS_UNCHANGED,
1937: 'OLD' || QP_PREQ_PUB.G_STATUS_UNCHANGED)
1938: AND getline.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
1939: , QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
1940: , QP_PREQ_PUB.G_STATUS_UNCHANGED)
1941: AND getline.price_flag = QP_PREQ_PUB.G_NO
1942: AND adj.price_adjustment_id NOT IN (SELECT price_adjustment_id
1943: FROM qp_int_ldets);
1936: 'NEW' || QP_PREQ_PUB.G_STATUS_UNCHANGED,
1937: 'OLD' || QP_PREQ_PUB.G_STATUS_UNCHANGED)
1938: AND getline.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
1939: , QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
1940: , QP_PREQ_PUB.G_STATUS_UNCHANGED)
1941: AND getline.price_flag = QP_PREQ_PUB.G_NO
1942: AND adj.price_adjustment_id NOT IN (SELECT price_adjustment_id
1943: FROM qp_int_ldets);
1944: --(getline.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE,QP_PREQ_PUB.G_CALCULATE_ONLY)
1937: 'OLD' || QP_PREQ_PUB.G_STATUS_UNCHANGED)
1938: AND getline.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED
1939: , QP_PREQ_PUB.G_STATUS_GSA_VIOLATION
1940: , QP_PREQ_PUB.G_STATUS_UNCHANGED)
1941: AND getline.price_flag = QP_PREQ_PUB.G_NO
1942: AND adj.price_adjustment_id NOT IN (SELECT price_adjustment_id
1943: FROM qp_int_ldets);
1944: --(getline.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE,QP_PREQ_PUB.G_CALCULATE_ONLY)
1945: --OR nvl(line.processed_code,'0') = QP_PREQ_PUB.G_BY_ENGINE)
1940: , QP_PREQ_PUB.G_STATUS_UNCHANGED)
1941: AND getline.price_flag = QP_PREQ_PUB.G_NO
1942: AND adj.price_adjustment_id NOT IN (SELECT price_adjustment_id
1943: FROM qp_int_ldets);
1944: --(getline.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE,QP_PREQ_PUB.G_CALCULATE_ONLY)
1945: --OR nvl(line.processed_code,'0') = QP_PREQ_PUB.G_BY_ENGINE)
1946: --and line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
1947:
1948: l_LINE_DETAIL_index QP_PREQ_GRP.PLS_INTEGER_TYPE;
1941: AND getline.price_flag = QP_PREQ_PUB.G_NO
1942: AND adj.price_adjustment_id NOT IN (SELECT price_adjustment_id
1943: FROM qp_int_ldets);
1944: --(getline.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE,QP_PREQ_PUB.G_CALCULATE_ONLY)
1945: --OR nvl(line.processed_code,'0') = QP_PREQ_PUB.G_BY_ENGINE)
1946: --and line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
1947:
1948: l_LINE_DETAIL_index QP_PREQ_GRP.PLS_INTEGER_TYPE;
1949: l_LINE_DETAIL_TYPE_CODE QP_PREQ_GRP.VARCHAR_TYPE;
1942: AND adj.price_adjustment_id NOT IN (SELECT price_adjustment_id
1943: FROM qp_int_ldets);
1944: --(getline.price_flag in (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE,QP_PREQ_PUB.G_CALCULATE_ONLY)
1945: --OR nvl(line.processed_code,'0') = QP_PREQ_PUB.G_BY_ENGINE)
1946: --and line.line_type_code = QP_PREQ_PUB.G_LINE_LEVEL
1947:
1948: l_LINE_DETAIL_index QP_PREQ_GRP.PLS_INTEGER_TYPE;
1949: l_LINE_DETAIL_TYPE_CODE QP_PREQ_GRP.VARCHAR_TYPE;
1950: l_PRICE_BREAK_TYPE_CODE QP_PREQ_GRP.VARCHAR_TYPE;
2020: --l_pricing_status_code QP_PREQ_GRP.VARCHAR_TYPE;
2021: --l_pricing_status_text QP_PREQ_GRP.VARCHAR_TYPE;
2022:
2023: l_debug VARCHAR2(3);
2024: l_pbh_adj_exists VARCHAR2(1) := QP_PREQ_PUB.G_NO;
2025: l_prg_adj_exists VARCHAR2(1) := QP_PREQ_PUB.G_NO;
2026:
2027: BEGIN
2028: x_return_status := FND_API.G_RET_STS_SUCCESS;
2021: --l_pricing_status_text QP_PREQ_GRP.VARCHAR_TYPE;
2022:
2023: l_debug VARCHAR2(3);
2024: l_pbh_adj_exists VARCHAR2(1) := QP_PREQ_PUB.G_NO;
2025: l_prg_adj_exists VARCHAR2(1) := QP_PREQ_PUB.G_NO;
2026:
2027: BEGIN
2028: x_return_status := FND_API.G_RET_STS_SUCCESS;
2029: l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
2028: x_return_status := FND_API.G_RET_STS_SUCCESS;
2029: l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
2030:
2031: --per bug3238607, no need to check HVOP
2032: IF(QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES ) THEN
2033: --QP_UTIL_PUB.HVOP_PRICING_ON = QP_PREQ_PUB.G_NO) THEN
2034: IF QP_PREQ_GRP.G_DEBUG_ENGINE = FND_API.G_TRUE THEN
2035: QP_PREQ_GRP.ENGINE_DEBUG('QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG=Y and ');
2036: --'QP_BULK_PREQ_GRP.G_HVOP_Pricing_ON=N');
2029: l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
2030:
2031: --per bug3238607, no need to check HVOP
2032: IF(QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES ) THEN
2033: --QP_UTIL_PUB.HVOP_PRICING_ON = QP_PREQ_PUB.G_NO) THEN
2034: IF QP_PREQ_GRP.G_DEBUG_ENGINE = FND_API.G_TRUE THEN
2035: QP_PREQ_GRP.ENGINE_DEBUG('QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG=Y and ');
2036: --'QP_BULK_PREQ_GRP.G_HVOP_Pricing_ON=N');
2037: QP_PREQ_GRP.ENGINE_DEBUG('QP_PREQ_PUB.G_GET_FREIGHT_FLAG:' || QP_PREQ_PUB.G_GET_FREIGHT_FLAG);
2031: --per bug3238607, no need to check HVOP
2032: IF(QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES ) THEN
2033: --QP_UTIL_PUB.HVOP_PRICING_ON = QP_PREQ_PUB.G_NO) THEN
2034: IF QP_PREQ_GRP.G_DEBUG_ENGINE = FND_API.G_TRUE THEN
2035: QP_PREQ_GRP.ENGINE_DEBUG('QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG=Y and ');
2036: --'QP_BULK_PREQ_GRP.G_HVOP_Pricing_ON=N');
2037: QP_PREQ_GRP.ENGINE_DEBUG('QP_PREQ_PUB.G_GET_FREIGHT_FLAG:' || QP_PREQ_PUB.G_GET_FREIGHT_FLAG);
2038: END IF;
2039:
2033: --QP_UTIL_PUB.HVOP_PRICING_ON = QP_PREQ_PUB.G_NO) THEN
2034: IF QP_PREQ_GRP.G_DEBUG_ENGINE = FND_API.G_TRUE THEN
2035: QP_PREQ_GRP.ENGINE_DEBUG('QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG=Y and ');
2036: --'QP_BULK_PREQ_GRP.G_HVOP_Pricing_ON=N');
2037: QP_PREQ_GRP.ENGINE_DEBUG('QP_PREQ_PUB.G_GET_FREIGHT_FLAG:' || QP_PREQ_PUB.G_GET_FREIGHT_FLAG);
2038: END IF;
2039:
2040: OPEN l_ldets_cur;
2041: LOOP
2350: QP_PREQ_GRP.engine_debug('Check l_pbh_adj_exists '|| l_pbh_adj_exists);
2351: QP_PREQ_GRP.engine_debug('Check l_prg_adj_exists '|| l_prg_adj_exists);
2352: END IF;
2353:
2354: --IF l_pbh_adj_exists = QP_PREQ_PUB.G_YES
2355: --THEN
2356: -- OPEN l_rltd_lines_cur;
2357: OPEN l_rltd_lines_cur(l_pbh_adj_exists, l_prg_adj_exists);
2358: LOOP
2416: , l_LIST_LINE_ID
2417: , l_RLTD_LIST_LINE_ID
2418: , l_pricing_status_text);
2419: /* delete from qp_npreq_rltd_lines_tmp
2420: where pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
2421: and relationship_type_code = QP_PREQ_PUB.G_PBH_LINE
2422: and pricing_status_text = G_CALC_INSERT;
2423: */ --need to delete them later
2424: END LOOP;
2417: , l_RLTD_LIST_LINE_ID
2418: , l_pricing_status_text);
2419: /* delete from qp_npreq_rltd_lines_tmp
2420: where pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
2421: and relationship_type_code = QP_PREQ_PUB.G_PBH_LINE
2422: and pricing_status_text = G_CALC_INSERT;
2423: */ --need to delete them later
2424: END LOOP;
2425: CLOSE l_rltd_lines_cur;
2727: END IF;
2728:
2729: END IF;
2730:
2731: --END IF;--l_pbh_adj_exists = QP_PREQ_PUB.G_YES
2732: END IF; --(QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES AND
2733: --QP_BULK_PREQ_GRP.G_HVOP_Pricing_ON = QP_PREQ_PUB.G_NO)
2734: END;
2735:
2728:
2729: END IF;
2730:
2731: --END IF;--l_pbh_adj_exists = QP_PREQ_PUB.G_YES
2732: END IF; --(QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES AND
2733: --QP_BULK_PREQ_GRP.G_HVOP_Pricing_ON = QP_PREQ_PUB.G_NO)
2734: END;
2735:
2736: --This procedure is to update the passed in overridden pbh modifiers
2729: END IF;
2730:
2731: --END IF;--l_pbh_adj_exists = QP_PREQ_PUB.G_YES
2732: END IF; --(QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES AND
2733: --QP_BULK_PREQ_GRP.G_HVOP_Pricing_ON = QP_PREQ_PUB.G_NO)
2734: END;
2735:
2736: --This procedure is to update the passed in overridden pbh modifiers
2737: --with the setup_value_from/to, qualifier_value, price_break_type_code
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;
2821:
2842: AND gsa_attr.attribute = G_GSA_ATTRIBUTE
2843: AND gsa_attr.value_from = G_YES;
2844:
2845: l_gsa_indicator VARCHAR2(1) := 'N';
2846: l_routine VARCHAR2(50) := 'QP_PREQ_PUB.CHECK_GSA_INDICATOR';
2847: BEGIN
2848: IF l_debug = FND_API.G_TRUE THEN
2849: QP_PREQ_GRP.engine_debug('Begin CHECK_GSA_INDICATOR '|| l_gsa_indicator);
2850:
3060:
3061: EXCEPTION
3062: WHEN OTHERS THEN
3063: IF l_debug = FND_API.G_TRUE THEN
3064: QP_PREQ_GRP.ENGINE_DEBUG('exception QP_PREQ_PUB.check gsa_violation: '|| SQLERRM);
3065: END IF;
3066: x_return_status := FND_API.G_RET_STS_ERROR;
3067: x_return_status_text := 'QP_PREQ_PUB.check gsa_violation: '|| SQLERRM;
3068: END CHECK_GSA_VIOLATION;
3063: IF l_debug = FND_API.G_TRUE THEN
3064: QP_PREQ_GRP.ENGINE_DEBUG('exception QP_PREQ_PUB.check gsa_violation: '|| SQLERRM);
3065: END IF;
3066: x_return_status := FND_API.G_RET_STS_ERROR;
3067: x_return_status_text := 'QP_PREQ_PUB.check gsa_violation: '|| SQLERRM;
3068: END CHECK_GSA_VIOLATION;
3069:
3070:
3071: PROCEDURE BACK_CALCULATION(p_line_index IN VARCHAR2
3546: IF l_debug = FND_API.G_TRUE THEN
3547: QP_PREQ_GRP.ENGINE_DEBUG('back cal fail set status');
3548: END IF;
3549: x_return_status := G_BACK_CALCULATION_STS;
3550: x_return_status_text := 'QP_PREQ_PUB: BACK CALCULATION FAILURE ';
3551: x_back_calc_rec := l_back_calc_rec;
3552: ELSE
3553: IF l_debug = FND_API.G_TRUE THEN
3554: QP_PREQ_GRP.ENGINE_DEBUG('back cal error set status');
3553: IF l_debug = FND_API.G_TRUE THEN
3554: QP_PREQ_GRP.ENGINE_DEBUG('back cal error set status');
3555: END IF;
3556: x_return_status := G_BACK_CALCULATION_STS;
3557: x_return_status_text := 'QP_PREQ_PUB: BACK CALCULATION ERROR '|| l_return_status_text;
3558: x_back_calc_rec := l_back_calc_rec;
3559: END IF;
3560: END IF;
3561:
3566: x_return_status := G_BACK_CALCULATION_STS;
3567: IF l_debug = FND_API.G_TRUE THEN
3568: QP_PREQ_GRP.ENGINE_DEBUG('BACK CALCULATION ROUTINE6');
3569: END IF;
3570: x_return_status_text := 'QP_PREQ_PUB.BACK_CAL: NO MANUAL ADJ';
3571: IF l_debug = FND_API.G_TRUE THEN
3572: QP_PREQ_GRP.ENGINE_DEBUG('BACK CALCULATION ROUTINE6.5');
3573: END IF;
3574: END IF;
3581: IF l_debug = FND_API.G_TRUE THEN
3582: QP_PREQ_GRP.ENGINE_DEBUG('BACK CALCULATION EXCEPTION '|| SQLERRM);
3583: END IF;
3584: x_return_status := FND_API.G_RET_STS_ERROR;
3585: x_return_status_text := 'Error QP_PREQ_PUB.BACK_CALCULATION '|| SQLERRM;
3586: END BACK_CALCULATION;
3587:
3588: --procedure to update the line's adjusted_unit_price with unit_price
3589: --if the engine is called with calculate_only and there are no adjustments
3664: l_amount_changed_tbl QP_PREQ_GRP.NUMBER_TYPE;
3665: l_line_unit_price_tbl QP_PREQ_GRP.NUMBER_TYPE;
3666: l_pricing_sts_code_tbl QP_PREQ_GRP.VARCHAR_TYPE;
3667: l_pricing_sts_text_tbl QP_PREQ_GRP.VARCHAR_TYPE;
3668: l_routine VARCHAR2(100) := 'QP_PREQ_PUB.UPDATE_UNIT_PRICE';
3669:
3670: l_return_status VARCHAR2(30);
3671: l_return_status_text VARCHAR2(240);
3672: l_ord_qty_operand NUMBER;
4101: --for bug 2812738 not to update if back_calc_error/gsa_violatn
4102: WHERE line.pricing_status_code = G_STATUS_UNCHANGED
4103: --fix for bug 3425569 where frozen lines were set to status updated
4104: --and processing constraints cause error in OM when they update frozen lines
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
4102: WHERE line.pricing_status_code = G_STATUS_UNCHANGED
4103: --fix for bug 3425569 where frozen lines were set to status updated
4104: --and processing constraints cause error in OM when they update frozen lines
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
4143: --for bug 2812738 not to update if back_calc_error/gsa_violatn
4144: WHERE line.pricing_status_code = G_STATUS_UNCHANGED
4145: --fix for bug 3425569 where frozen lines were set to status updated
4146: --and processing constraints cause error in OM when they update frozen lines
4147: AND (line.price_flag IN (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
4148: OR nvl(line.processed_code, '0') = QP_PREQ_PUB.G_BY_ENGINE)
4149: AND (G_CALCULATE_FLAG = G_CALCULATE_ONLY
4150:
4151: OR EXISTS (SELECT 'Y' FROM qp_int_ldets ldet
4144: WHERE line.pricing_status_code = G_STATUS_UNCHANGED
4145: --fix for bug 3425569 where frozen lines were set to status updated
4146: --and processing constraints cause error in OM when they update frozen lines
4147: AND (line.price_flag IN (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
4148: OR nvl(line.processed_code, '0') = QP_PREQ_PUB.G_BY_ENGINE)
4149: AND (G_CALCULATE_FLAG = G_CALCULATE_ONLY
4150:
4151: OR EXISTS (SELECT 'Y' FROM qp_int_ldets ldet
4152: WHERE ldet.line_index = line.line_index
4184:
4185: EXCEPTION
4186: WHEN OTHERS THEN
4187: x_return_status := FND_API.G_RET_STS_ERROR;
4188: x_return_status_text := 'Exception in QP_PREQ_PUB.Update_Line_Status: '|| SQLERRM;
4189: END Update_Line_Status;
4190:
4191: --overloaded for QP.G reqts
4192: --this is used in performance code path called from QPXVCLNB.fetch_adjustments
4194: PROCEDURE CALCULATE_PRICE(p_request_type_code IN VARCHAR2,
4195: p_rounding_flag IN VARCHAR2,
4196: p_view_name IN VARCHAR2,
4197: p_event_code IN VARCHAR2,
4198: p_adj_tbl IN QP_PREQ_PUB.ADJ_TBL_TYPE,
4199: x_return_status OUT NOCOPY VARCHAR2,
4200: x_return_status_text OUT NOCOPY VARCHAR2) IS
4201:
4202: --fix for bug 2515762 to print in debug the rltd info
4248: l_return_adjustment NUMBER := 0;
4249: l_sign NUMBER := 1;
4250: l_return_status VARCHAR2(30);
4251: l_return_status_text VARCHAR2(240);
4252: l_routine VARCHAR2(50) := 'Routine :QP_PREQ_PUB.Calculate_price ';
4253: --l_processed_flag VARCHAR2(1);
4254: --l_pbh_request_qty NUMBER :=0;
4255:
4256: --l_pbh_net_adj_amount NUMBER := 0; -- 2892848 no need
4261: x PLS_INTEGER := 0; -- 3126019
4262: y PLS_INTEGER;
4263: l_tbl_index PLS_INTEGER;
4264:
4265: l_adj_tbl QP_PREQ_PUB.adj_tbl_type;
4266: l_frt_tbl FRT_CHARGE_TBL;
4267: l_back_calc_ret_rec back_calc_rec_type;
4268:
4269: l_ldet_line_dtl_index_tbl QP_PREQ_GRP.NUMBER_TYPE;
4414: AND qla.attribute = qpa.product_attribute
4415: AND qla.value_from = qpa.product_attr_value
4416: AND qla.line_index = ql.line_index
4417: AND ql.price_flag <> G_PHASE
4418: AND ql.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED,
4419: QP_PREQ_PUB.G_STATUS_GSA_VIOLATION,
4420: QP_PREQ_PUB.G_STATUS_UNCHANGED)
4421: AND NOT EXISTS (SELECT qla2.line_index
4422: FROM qp_preq_line_attrs_tmp qla2, qp_pricing_attributes qpa2
4415: AND qla.value_from = qpa.product_attr_value
4416: AND qla.line_index = ql.line_index
4417: AND ql.price_flag <> G_PHASE
4418: AND ql.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED,
4419: QP_PREQ_PUB.G_STATUS_GSA_VIOLATION,
4420: QP_PREQ_PUB.G_STATUS_UNCHANGED)
4421: AND NOT EXISTS (SELECT qla2.line_index
4422: FROM qp_preq_line_attrs_tmp qla2, qp_pricing_attributes qpa2
4423: WHERE qpa2.list_line_id = p_list_line_id
4416: AND qla.line_index = ql.line_index
4417: AND ql.price_flag <> G_PHASE
4418: AND ql.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED,
4419: QP_PREQ_PUB.G_STATUS_GSA_VIOLATION,
4420: QP_PREQ_PUB.G_STATUS_UNCHANGED)
4421: AND NOT EXISTS (SELECT qla2.line_index
4422: FROM qp_preq_line_attrs_tmp qla2, qp_pricing_attributes qpa2
4423: WHERE qpa2.list_line_id = p_list_line_id
4424: AND qpa2.excluder_flag = G_YES
7173:
7174: END CALCULATE_PRICE;
7175:
7176: --this is used in pl/sql code path for OKC/OKS/ASO
7177: --this is called from QP_PREQ_PUB.price_request
7178:
7179: PROCEDURE CALCULATE_PRICE(p_rounding_flag IN VARCHAR2,
7180: x_return_status OUT NOCOPY VARCHAR2,
7181: x_return_status_text OUT NOCOPY VARCHAR2) IS
7180: x_return_status OUT NOCOPY VARCHAR2,
7181: x_return_status_text OUT NOCOPY VARCHAR2) IS
7182:
7183: /*
7184: INDX,QP_PREQ_PUB.calculate_price.l_line_cur,qp_npreq_lines_tmp_N2,LINE_TYPE_CODE,1
7185: */
7186: -- for bug 3820859, to start with adjusted_unit_price should be same as unit_price
7187: -- if not, then the same discount is getting applied twice if limit profile is set, because discounts calculations happens twice -
7188: -- once from QP_PREQ_GRP and then from QP_PREQ_PUB
7184: INDX,QP_PREQ_PUB.calculate_price.l_line_cur,qp_npreq_lines_tmp_N2,LINE_TYPE_CODE,1
7185: */
7186: -- for bug 3820859, to start with adjusted_unit_price should be same as unit_price
7187: -- if not, then the same discount is getting applied twice if limit profile is set, because discounts calculations happens twice -
7188: -- once from QP_PREQ_GRP and then from QP_PREQ_PUB
7189: CURSOR l_line_cur IS SELECT
7190: line.line_index
7191: , line.unit_price adjusted_unit_price -- bug 3820859
7192: , line.unit_price
7206: AND line.usage_pricing_type IN
7207: (QP_PREQ_GRP.G_BILLING_TYPE, QP_PREQ_GRP.G_REGULAR_USAGE_TYPE);
7208:
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
7207: (QP_PREQ_GRP.G_BILLING_TYPE, QP_PREQ_GRP.G_REGULAR_USAGE_TYPE);
7208:
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: */
7208:
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
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
7467: -- end 2892848
7468:
7469: l_return_status VARCHAR2(30);
7470: l_return_status_text VARCHAR2(240);
7471: l_routine VARCHAR2(50) := 'Routine :QP_PREQ_PUB.Calculate_price ';
7472:
7473: -- [julin/3265308] net amount calculation 'P', match product only.
7474: -- given a line line id, find the product attribute and context for
7475: -- the modifier and match all request lines with that context/attr
7483: AND qla.attribute = qpa.product_attribute
7484: AND qla.value_from = qpa.product_attr_value
7485: AND qla.line_index = ql.line_index
7486: AND ql.price_flag <> G_PHASE
7487: AND ql.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED,
7488: QP_PREQ_PUB.G_STATUS_GSA_VIOLATION,
7489: QP_PREQ_PUB.G_STATUS_UNCHANGED)
7490: AND NOT EXISTS (SELECT qla2.line_index
7491: FROM qp_preq_line_attrs_tmp qla2, qp_pricing_attributes qpa2
7484: AND qla.value_from = qpa.product_attr_value
7485: AND qla.line_index = ql.line_index
7486: AND ql.price_flag <> G_PHASE
7487: AND ql.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED,
7488: QP_PREQ_PUB.G_STATUS_GSA_VIOLATION,
7489: QP_PREQ_PUB.G_STATUS_UNCHANGED)
7490: AND NOT EXISTS (SELECT qla2.line_index
7491: FROM qp_preq_line_attrs_tmp qla2, qp_pricing_attributes qpa2
7492: WHERE qpa2.list_line_id = p_list_line_id
7485: AND qla.line_index = ql.line_index
7486: AND ql.price_flag <> G_PHASE
7487: AND ql.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED,
7488: QP_PREQ_PUB.G_STATUS_GSA_VIOLATION,
7489: QP_PREQ_PUB.G_STATUS_UNCHANGED)
7490: AND NOT EXISTS (SELECT qla2.line_index
7491: FROM qp_preq_line_attrs_tmp qla2, qp_pricing_attributes qpa2
7492: WHERE qpa2.list_line_id = p_list_line_id
7493: AND qpa2.excluder_flag = G_YES
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,
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
9082: QP_PREQ_GRP.ENGINE_DEBUG('Back calculate Failure');
9083: END IF;
9084: l_lines_tbl(j).processed_flag := G_NOT_PROCESSED;
9085: l_lines_tbl(j).processed_code :=
9086: QP_PREQ_PUB.G_BACK_CALCULATION_STS;
9087: l_lines_tbl(j).pricing_status_code :=
9088: QP_PREQ_PUB.G_BACK_CALCULATION_STS;
9089: l_lines_tbl(j).pricing_status_text := l_return_status_text;
9090: END IF; --l_return_status
9084: l_lines_tbl(j).processed_flag := G_NOT_PROCESSED;
9085: l_lines_tbl(j).processed_code :=
9086: QP_PREQ_PUB.G_BACK_CALCULATION_STS;
9087: l_lines_tbl(j).pricing_status_code :=
9088: QP_PREQ_PUB.G_BACK_CALCULATION_STS;
9089: l_lines_tbl(j).pricing_status_text := l_return_status_text;
9090: END IF; --l_return_status
9091: ELSE
9092: IF l_debug = FND_API.G_TRUE THEN
9097:
9098:
9099: IF l_lines_tbl(j).processed_flag = G_PROCESSED THEN
9100: /*
9101: INDX,QP_PREQ_PUB.calculate_price.upd3,qp_npreq_lines_tmp_N1,LINE_INDEX,1
9102: */
9103: UPDATE qp_npreq_lines_tmp SET adjusted_unit_price
9104: = nvl(l_lines_tbl(j).updated_adjusted_unit_price,
9105: l_lines_tbl(j).adjusted_unit_price)
9122: 'processed_code' || l_lines_tbl(j).processed_code);
9123: END IF;
9124: ELSE
9125: /*
9126: INDX,QP_PREQ_PUB.calculate_price.upd4,qp_npreq_lines_tmp_N1,LINE_INDEX,1
9127: */
9128: UPDATE qp_npreq_lines_tmp SET adjusted_unit_price
9129: = l_lines_tbl(j).adjusted_unit_price
9130: --, adjusted_unit_price_ur = l_adjusted_unit_price_ur(j) --[prarasto:Post Round] added unrounded adjusted unit price, [julin/postround] redesign
9183: QP_PREQ_GRP.engine_debug('Begin Pub GSA check');
9184:
9185: END IF;
9186: /*
9187: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_lines_tmp_N2,LINE_TYPE_CODE,1
9188: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N2,LINE_TYPE_CODE,1
9189: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N1,LINE_INDEX,1
9190: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N1,ATTRIBUTE_TYPE,2
9191: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
9184:
9185: END IF;
9186: /*
9187: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_lines_tmp_N2,LINE_TYPE_CODE,1
9188: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N2,LINE_TYPE_CODE,1
9189: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N1,LINE_INDEX,1
9190: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N1,ATTRIBUTE_TYPE,2
9191: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
9192: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
9185: END IF;
9186: /*
9187: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_lines_tmp_N2,LINE_TYPE_CODE,1
9188: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N2,LINE_TYPE_CODE,1
9189: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N1,LINE_INDEX,1
9190: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N1,ATTRIBUTE_TYPE,2
9191: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
9192: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
9193: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
9186: /*
9187: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_lines_tmp_N2,LINE_TYPE_CODE,1
9188: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N2,LINE_TYPE_CODE,1
9189: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N1,LINE_INDEX,1
9190: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N1,ATTRIBUTE_TYPE,2
9191: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
9192: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
9193: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
9194: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
9187: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_lines_tmp_N2,LINE_TYPE_CODE,1
9188: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N2,LINE_TYPE_CODE,1
9189: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N1,LINE_INDEX,1
9190: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N1,ATTRIBUTE_TYPE,2
9191: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
9192: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
9193: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
9194: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
9195: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_VALUE,5
9188: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N2,LINE_TYPE_CODE,1
9189: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N1,LINE_INDEX,1
9190: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N1,ATTRIBUTE_TYPE,2
9191: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
9192: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
9193: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
9194: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
9195: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_VALUE,5
9196: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
9189: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N1,LINE_INDEX,1
9190: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N1,ATTRIBUTE_TYPE,2
9191: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
9192: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
9193: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
9194: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
9195: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_VALUE,5
9196: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
9197: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
9190: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N1,ATTRIBUTE_TYPE,2
9191: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
9192: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
9193: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
9194: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
9195: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_VALUE,5
9196: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
9197: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
9198: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
9191: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
9192: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
9193: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
9194: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
9195: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_VALUE,5
9196: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
9197: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
9198: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
9199: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
9192: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
9193: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
9194: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
9195: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_VALUE,5
9196: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
9197: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
9198: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
9199: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
9200: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,ACTIVE_FLAG,1
9193: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
9194: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
9195: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_VALUE,5
9196: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
9197: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
9198: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
9199: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
9200: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,ACTIVE_FLAG,1
9201: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,CURRENCY_CODE,2
9194: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
9195: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_VALUE,5
9196: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
9197: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
9198: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
9199: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
9200: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,ACTIVE_FLAG,1
9201: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,CURRENCY_CODE,2
9202: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,LIST_TYPE_CODE,3
9195: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_VALUE,5
9196: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
9197: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
9198: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
9199: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
9200: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,ACTIVE_FLAG,1
9201: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,CURRENCY_CODE,2
9202: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,LIST_TYPE_CODE,3
9203: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,SOURCE_SYSTEM_CODE,4
9196: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
9197: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
9198: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
9199: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
9200: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,ACTIVE_FLAG,1
9201: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,CURRENCY_CODE,2
9202: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,LIST_TYPE_CODE,3
9203: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,SOURCE_SYSTEM_CODE,4
9204: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,LIST_HEADER_ID,5
9197: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
9198: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
9199: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
9200: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,ACTIVE_FLAG,1
9201: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,CURRENCY_CODE,2
9202: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,LIST_TYPE_CODE,3
9203: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,SOURCE_SYSTEM_CODE,4
9204: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,LIST_HEADER_ID,5
9205: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,GSA_INDICATOR,6
9198: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
9199: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
9200: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,ACTIVE_FLAG,1
9201: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,CURRENCY_CODE,2
9202: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,LIST_TYPE_CODE,3
9203: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,SOURCE_SYSTEM_CODE,4
9204: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,LIST_HEADER_ID,5
9205: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,GSA_INDICATOR,6
9206: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_LINES_PK,LIST_LINE_ID,1
9199: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
9200: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,ACTIVE_FLAG,1
9201: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,CURRENCY_CODE,2
9202: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,LIST_TYPE_CODE,3
9203: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,SOURCE_SYSTEM_CODE,4
9204: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,LIST_HEADER_ID,5
9205: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,GSA_INDICATOR,6
9206: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_LINES_PK,LIST_LINE_ID,1
9207: */
9200: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,ACTIVE_FLAG,1
9201: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,CURRENCY_CODE,2
9202: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,LIST_TYPE_CODE,3
9203: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,SOURCE_SYSTEM_CODE,4
9204: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,LIST_HEADER_ID,5
9205: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,GSA_INDICATOR,6
9206: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_LINES_PK,LIST_LINE_ID,1
9207: */
9208: IF l_debug = FND_API.G_TRUE THEN
9201: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,CURRENCY_CODE,2
9202: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,LIST_TYPE_CODE,3
9203: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,SOURCE_SYSTEM_CODE,4
9204: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,LIST_HEADER_ID,5
9205: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,GSA_INDICATOR,6
9206: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_LINES_PK,LIST_LINE_ID,1
9207: */
9208: IF l_debug = FND_API.G_TRUE THEN
9209: QP_PREQ_GRP.engine_debug('Pub GSA attr_mgr_installed '
9202: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,LIST_TYPE_CODE,3
9203: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,SOURCE_SYSTEM_CODE,4
9204: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,LIST_HEADER_ID,5
9205: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,GSA_INDICATOR,6
9206: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_LINES_PK,LIST_LINE_ID,1
9207: */
9208: IF l_debug = FND_API.G_TRUE THEN
9209: QP_PREQ_GRP.engine_debug('Pub GSA attr_mgr_installed '
9210: || G_ATTR_MGR_INSTALLED);
9212: IF G_ATTR_MGR_INSTALLED = G_NO
9213: THEN
9214: UPDATE qp_npreq_lines_tmp line
9215: SET line.pricing_status_code = G_STATUS_GSA_VIOLATION
9216: , line.pricing_status_text = 'QP_PREQ_PUB: GSA VIOLATION'
9217: WHERE line.line_type_code = G_LINE_LEVEL
9218: --made this change look at only successful lines
9219: AND line.pricing_status_code IN
9220: (G_STATUS_UPDATED, G_STATUS_UNCHANGED)
9266: , line.pricing_effective_date)));
9267: ELSE --G_ATTR_MGR_INSTALLED
9268: UPDATE qp_npreq_lines_tmp line
9269: SET line.pricing_status_code = G_STATUS_GSA_VIOLATION
9270: , line.pricing_status_text = 'QP_PREQ_PUB: GSA VIOLATION'
9271: WHERE line.line_type_code = G_LINE_LEVEL
9272: --made this change look at only successful lines
9273: AND line.pricing_status_code IN
9274: (G_STATUS_UPDATED, G_STATUS_UNCHANGED)
9412: END IF;
9413: EXCEPTION
9414: WHEN OTHERS THEN
9415: x_return_status := FND_API.G_RET_STS_ERROR;
9416: x_return_status_text := 'Exception in QP_PREQ_PUB.Cleanup_rltd_lines '|| SQLERRM;
9417: END Cleanup_rltd_lines;
9418:
9419:
9420: --coded for bug 2264566
9559: END IF;
9560: EXCEPTION
9561: WHEN OTHERS THEN
9562: x_return_status := FND_API.G_RET_STS_ERROR;
9563: x_return_status_text := 'Exception in QP_PREQ_PUB.Update_Related_Line_Info '|| SQLERRM;
9564: END Update_Related_Line_Info;
9565:
9566:
9567: --This is to cleanup duplicate adjustments between user passed
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
9607:
9608: TYPE DUP_ADJ_TBL_TYPE IS TABLE OF l_duplicate_rec%TYPE INDEX BY BINARY_INTEGER;
9609:
9610: l_dup_adj_tbl DUP_ADJ_TBL_TYPE;
9611: l_routine VARCHAR2(100) := 'Routine:QP_PREQ_PUB.PROCESS_ADJUSTMENTS';
9612:
9613: /*
9614: indxno index used
9615: */
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
9689: --event passed in the control record
9690:
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
9821: END IF;
9822: l_duplicate_rec.process_code := G_STATUS_NEW;
9823: l_duplicate_rec1.process_code := G_STATUS_UNCHANGED;
9824: l_duplicate_rec1.pricing_status_code := G_STATUS_DELETED;
9825: l_duplicate_rec1.pricing_status_text := 'QP_PREQ_PUB:DUPLICATE RECORD';
9826:
9827: ELSIF (l_duplicate_rec.pricing_status_code = G_STATUS_UNCHANGED AND
9828: ((nvl(l_duplicate_rec.updated_flag, G_NO) = G_NO) OR
9829: (nvl(l_duplicate_rec.updated_flag, G_NO) = G_NO))) THEN
9832: QP_PREQ_GRP.ENGINE_DEBUG('retain engine passed');
9833: END IF;
9834: l_duplicate_rec.process_code := G_STATUS_UNCHANGED;
9835: l_duplicate_rec.pricing_status_code := G_STATUS_DELETED;
9836: l_duplicate_rec1.pricing_status_text := 'QP_PREQ_PUB:DUPLICATE RECORD';
9837: l_duplicate_rec1.process_code := G_STATUS_NEW;
9838: ELSE
9839: --retain adj passed by engine
9840: IF l_debug = FND_API.G_TRUE THEN
9842:
9843: END IF;
9844: l_duplicate_rec.process_code := G_STATUS_UNCHANGED;
9845: l_duplicate_rec.pricing_status_code := G_STATUS_DELETED;
9846: l_duplicate_rec.pricing_status_text := 'QP_PREQ_PUB:DUPLICATE RECORD';
9847: l_duplicate_rec1.process_code := G_STATUS_NEW;
9848: END IF;
9849: ELSE
9850: NULL;
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
10015: IF l_debug = FND_API.G_TRUE THEN
10016: QP_PREQ_GRP.engine_debug('Error UPDATE_QUALIFIER_VALUE routine '|| SQLERRM);
10017: END IF;
10018: x_return_status := FND_API.G_RET_STS_ERROR;
10019: x_return_status_text := 'QP_PREQ_PUB.UPDATE_QUALIFIER_VALUE: '|| SQLERRM;
10020: END UPDATE_QUALIFIER_VALUE;
10021:
10022: --This is to update the status on the child break lines after calculation
10023: PROCEDURE Update_Child_Break_Lines(x_return_status OUT NOCOPY VARCHAR2,
10108: IF l_debug = FND_API.G_TRUE THEN
10109: QP_PREQ_GRP.engine_debug('Error Update_Child_break_lines routine '|| SQLERRM);
10110: END IF;
10111: x_return_status := FND_API.G_RET_STS_ERROR;
10112: x_return_status_text := 'QP_PREQ_PUB.Update_Child_Break_lines: '|| SQLERRM;
10113:
10114: END Update_Child_Break_Lines;
10115:
10116:
10494: END IF;
10495: EXCEPTION
10496: WHEN OTHERS THEN
10497: x_return_status := FND_API.G_RET_STS_ERROR;
10498: x_return_status_text := 'QP_PREQ_PUB.Process_Price_Break Exception: '
10499: || SQLERRM;
10500: IF l_debug = FND_API.G_TRUE THEN
10501: QP_PREQ_GRP.engine_debug('Exception process_price_break '|| SQLERRM);
10502: END IF;
10504:
10505: PROCEDURE Update_Service_Lines(x_return_status OUT NOCOPY VARCHAR2,
10506: x_return_status_text OUT NOCOPY VARCHAR2) IS
10507: /*
10508: INDX,QP_PREQ_PUB.Usage_pricing.l_Service_Cur,-No Index Used-,NA,NA
10509: */
10510: CURSOR l_Service_Cur IS
10511: SELECT rltd.line_index,
10512: rltd.related_line_index,
10562: END IF;
10563: EXCEPTION
10564: WHEN OTHERS THEN
10565: x_return_status := FND_API.G_RET_STS_ERROR;
10566: x_return_status_text := 'QP_PREQ_PUB.Update_Service_Lines Exception: '
10567: || SQLERRM;
10568: IF l_debug = FND_API.G_TRUE THEN
10569: QP_PREQ_GRP.engine_debug('Exception Update_Service_Lines '|| SQLERRM);
10570: END IF;
10859: IF l_debug = FND_API.G_TRUE THEN
10860: QP_PREQ_GRP.engine_debug('Exception Usage Pricing '|| SQLERRM);
10861: END IF;
10862: x_return_status := FND_API.G_RET_STS_ERROR;
10863: x_return_status_text := 'QP_PREQ_PUB.Usage_Pricing Exception: '
10864: || SQLERRM;
10865: END Usage_pricing;
10866:
10867: FUNCTION Call_Usage_Pricing RETURN VARCHAR2 IS
10881: RETURN x_call_usage_pricing;
10882: EXCEPTION
10883: WHEN OTHERS THEN
10884: IF l_debug = FND_API.G_TRUE THEN
10885: QP_PREQ_GRP.engine_debug('QP_PREQ_PUB.Call_Usage_Pricing Exception: '|| SQLERRM);
10886: END IF;
10887: x_call_usage_pricing := NULL;
10888: END Call_Usage_Pricing;
10889:
10918: --bug 3085453 handle pricing availability UI
10919: -- they pass reqtype ONT and insert adj into ldets
10920: --bug 3085453 handle pricing availability UI
10921: -- they pass reqtype ONT and insert adj into ldets
10922: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = G_YES
10923: --need not do freegoods cleanup if PRG line has price_flag 'P' or 'N'
10924: AND buyline.price_flag = G_YES --in (G_YES, G_PHASE)
10925: AND instr(p_event_code, ev.pricing_event_code || ',') > 0
10926: AND ph.pricing_phase_id = ev.pricing_phase_id
10971: -- , qp_npreq_lines_tmp oldfreeline
10972: --where G_REQUEST_TYPE_CODE = 'ONT'
10973: --bug 3085453 handle pricing availability UI
10974: -- they pass reqtype ONT and insert adj into ldets
10975: -- WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = G_YES
10976: --need not do freegoods cleanup if PRG line has price_flag 'P' or 'N'
10977: -- AND buyline.price_flag = G_YES --in (G_YES, G_PHASE)
10978: -- AND instr(p_event_code, ev.pricing_event_code || ',') > 0
10979: -- AND ph.pricing_phase_id = ev.pricing_phase_id
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
11026: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG <> G_YES
11027: AND buyline.price_flag = G_YES --in (G_YES, G_PHASE)
11028: AND instr(p_event_code, ev.pricing_event_code || ',') > 0
11029: AND ph.pricing_phase_id = ev.pricing_phase_id
11030: AND NVL(ev.start_date_active,sysdate) >= SYSDATE --9446733
11090: AND ass.price_adjustment_id = prg.price_adjustment_id
11091: AND fgdis.price_adjustment_id = ass.rltd_price_adj_id;
11092:
11093: l_fgdline_exist NUMBER := 0; --bug 14396941
11094: l_routine VARCHAR2(100) := 'QP_PREQ_PUB.Identify_freegood_lines ';
11095: BEGIN
11096: l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
11097: IF l_debug = FND_API.G_TRUE THEN
11098: QP_PREQ_GRP.engine_debug('Begin Identify_freegood_lines: '|| p_event_code);
11128: , oe_price_adjustments oldfgdis
11129: -- where G_REQUEST_TYPE_CODE = 'ONT'
11130: --bug 3085453 handle pricing availability UI
11131: -- they pass reqtype ONT and insert adj into ldets
11132: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = G_YES
11133: AND instr(p_event_code, ev.pricing_event_code || ',') > 0
11134: AND ph.pricing_phase_id = ev.pricing_phase_id
11135: AND ((buyline.line_type_code = G_LINE_LEVEL
11136: AND oldprg.line_id = buyline.line_id)
11156: END LOOP; --for cl
11157: END IF; --debug
11158:
11159: --bug 14396941 start
11160: IF QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG <> G_YES
11161: THEN
11162: l_fgdline_exist := 1;
11163:
11164: begin
11294: l_mark_fgdis_delete_index QP_PREQ_GRP.number_type;
11295: l_prg_list_line_id NUMBER;
11296:
11297: l_debug VARCHAR2(3);
11298: l_routine VARCHAR2(50) := 'QP_PREQ_PUB.Update_prg_pricing_status';
11299: BEGIN
11300: l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
11301: IF l_debug = FND_API.G_TRUE THEN
11302: QP_PREQ_GRP.engine_debug('Begin Update_prg_pricing_status');
11323: QP_PREQ_GRP.engine_debug('Mark delete prg list_line_id -1');
11324: END;
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;
11488: , qp_npreq_line_attrs_tmp newfgitem
11489: --where G_REQUEST_TYPE_CODE = 'ONT'
11490: --bug 3085453 handle pricing availability UI
11491: -- they pass reqtype ONT and insert adj into ldets
11492: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = G_YES
11493: AND instr(oldfreeline.process_status, G_FREEGOOD) > 0
11494: AND newrltd.pricing_status_code = G_STATUS_NEW
11495: AND instr(oldfreeline.process_status
11496: , G_BUYLINE || newrltd.line_index || G_PROMO_GOODS_DISCOUNT) > 0
11612: , qp_npreq_line_attrs_tmp oldfreeitem
11613: --WHERE G_REQUEST_TYPE_CODE <> 'ONT'
11614: --bug 3085453 handle pricing availability UI
11615: -- they pass reqtype ONT and insert adj into ldets
11616: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG <> G_YES
11617: AND instr(oldfreeline.process_status, G_FREEGOOD) > 0
11618: AND newfgdis.pricing_status_code = G_STATUS_NEW
11619: AND newfgdis.applied_flag = G_YES
11620: AND instr(oldfreeline.process_status, G_FREEGOOD || newfgdis.created_from_list_line_id || G_BUYLINE) > 0
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
11644: AND buyline.line_type_code = G_LINE_LEVEL
11645: AND prg.line_id = buyline.line_id
11646: AND prg.list_line_type_code = G_PROMO_GOODS_DISCOUNT
11647: AND prg.updated_flag = G_YES
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
11663: AND buyline.line_type_code = G_ORDER_LEVEL
11664: AND prg.line_id IS NULL
11665: AND prg.header_id = buyline.line_id
11666: AND prg.list_line_type_code = G_PROMO_GOODS_DISCOUNT
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
11684: AND prg.line_index = buyline.line_index
11685: AND prg.pricing_status_code = G_STATUS_UNCHANGED
11686: AND prg.created_from_list_line_type = G_PROMO_GOODS_DISCOUNT
11687: AND prg.updated_flag = G_YES
11700:
11701: l_upd_engine_fg_index QP_PREQ_GRP.NUMBER_TYPE;
11702:
11703: l_Process_PRG VARCHAR2(1) := G_NO;
11704: l_routine VARCHAR2(100) := 'QP_PREQ_PUB.Process_PRG ';
11705: l_oldfgdis_list_line_id VARCHAR2(2000);
11706: l_old_operand VARCHAR2(240);
11707:
11708: i PLS_INTEGER;
12142: QP_PREQ_GRP.engine_debug('End process_prg');
12143: END IF;
12144: END IF; --l_process_prg
12145: -- service project
12146: IF QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG <> G_YES THEN
12147: UPDATE qp_npreq_lines_tmp SET pricing_status_code = G_NOT_VALID, process_status = G_NOT_VALID
12148: WHERE PROCESSED_CODE = G_BY_ENGINE AND SERVICE_PERIOD IS NOT NULL;
12149: QP_PREQ_GRP.engine_debug('deleted service rows'||SQL%rowcount);
12150: END IF;
12157: -- Bug 3348208
12158: -- the following if condition for check_cust_view_flag is commented out so that
12159: -- update_prg_pricing_status is always run. Previously, it was introduced only
12160: -- for ASO, not OM, but we discovered it needs to go for OM as well.
12161: --IF QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG <> G_YES THEN
12162: update_prg_pricing_status(x_return_status, x_return_status_text);
12163:
12164: x_return_status := FND_API.G_RET_STS_SUCCESS;
12165: x_return_status_text := NULL;
12183: --If the selling price changed, then it marks the newly created line
12184: --as UPDATED, else the passed in freegood line is marked as unchanged
12185: PROCEDURE Update_PRG_Process_status(x_return_status OUT NOCOPY VARCHAR2,
12186: x_return_status_text OUT NOCOPY VARCHAR2) IS
12187: l_routine VARCHAR2(100) := 'QP_PREQ_PUB.Update_PRG_Process_status ';
12188:
12189: BEGIN
12190: l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
12191: IF l_debug = FND_API.G_TRUE THEN
12243: --bug 2970368
12244: --IF G_REQUEST_TYPE_CODE <> 'ONT' THEN
12245: --bug 3085453 handle pricing availability UI
12246: -- they pass reqtype ONT and insert adj into ldets
12247: IF QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG <> G_YES THEN
12248: update_prg_pricing_status(x_return_status, x_return_status_text);
12249: END IF; --G_REQUEST_TYPE_CODE
12250:
12251: x_return_status := FND_API.G_RET_STS_SUCCESS;
12286: AND dis.line_detail_index = rltd.related_line_detail_index
12287: AND dis.pricing_status_code = G_STATUS_NEW;
12288:
12289: I PLS_INTEGER;
12290: l_routine VARCHAR2(100) := 'QP_PREQ_PUB.Populate_buy_line_price_flag';
12291: BEGIN
12292: IF l_debug = FND_API.G_TRUE THEN
12293: QP_PREQ_GRP.engine_debug('Begin Populate_buy_line_price_flag');
12294:
12388: p_line_index IN NUMBER)
12389: RETURN VARCHAR2 IS
12390:
12391: i PLS_INTEGER;
12392: l_routine VARCHAR2(100) := 'QP_PREQ_PUB.Get_buy_line_price_flag';
12393: l_count NUMBER;
12394: BEGIN
12395: IF l_debug = FND_API.G_TRUE THEN
12396: QP_PREQ_GRP.engine_debug('Begin Get_buy_line_price_flag, p_list_line_id p_line_index: '|| p_list_line_id || ' ' || p_line_index);
12485:
12486: EXCEPTION
12487: WHEN Coupon_Exception THEN
12488: IF l_debug = FND_API.G_TRUE THEN
12489: QP_PREQ_GRP.engine_debug('Exception in QP_PREQ_PUB.Process_coupon_issue '|| x_return_status_text);
12490: END IF;
12491: WHEN OTHERS THEN
12492: x_return_status := FND_API.G_RET_STS_ERROR;
12493: x_return_status_text := 'Exception in QP_PREQ_PUB.Process_coupon_issue '|| SQLERRM;
12489: QP_PREQ_GRP.engine_debug('Exception in QP_PREQ_PUB.Process_coupon_issue '|| x_return_status_text);
12490: END IF;
12491: WHEN OTHERS THEN
12492: x_return_status := FND_API.G_RET_STS_ERROR;
12493: x_return_status_text := 'Exception in QP_PREQ_PUB.Process_coupon_issue '|| SQLERRM;
12494: IF l_debug = FND_API.G_TRUE THEN
12495: QP_PREQ_GRP.engine_debug(x_return_status_text);
12496: END IF;
12497: END Process_coupon_issue;
12539: l_recur_benefit_qty_tbl QP_PREQ_GRP.number_type; --Bug 2804053
12540: l_recur_benefit_qty NUMBER;
12541: l_recur_base_qty NUMBER;
12542: l_recur_qualifying_qty NUMBER;
12543: l_routine VARCHAR2(100) := 'QP_PREQ_PUB.Update_recurring_quantity';
12544:
12545: BEGIN
12546: l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
12547: l_recur_dtl_index_tbl.DELETE;
12633: PROCEDURE Populate_Output_INT(x_return_status OUT NOCOPY VARCHAR2,
12634: x_return_status_text OUT NOCOPY VARCHAR2) IS
12635:
12636: /*
12637: INDX,QP_PREQ_PUB.populate_output_int.l_lines_cur,- No Index Used -,NA,NA
12638: */
12639: CURSOR l_lines_cur IS
12640: SELECT LINE_INDEX,
12641: LINE_ID,
12861: END IF;
12862: EXCEPTION
12863: WHEN OTHERS THEN
12864: IF l_debug = FND_API.G_TRUE THEN
12865: QP_PREQ_GRP.engine_debug('QP_PREQ_PUB.Populate_Output_INT: '|| SQLERRM);
12866: END IF;
12867: x_return_status := FND_API.G_RET_STS_ERROR;
12868: x_return_status_text := 'QP_PREQ_PUB.Populate_Output_INT: '|| SQLERRM;
12869: END Populate_Output_INT;
12864: IF l_debug = FND_API.G_TRUE THEN
12865: QP_PREQ_GRP.engine_debug('QP_PREQ_PUB.Populate_Output_INT: '|| SQLERRM);
12866: END IF;
12867: x_return_status := FND_API.G_RET_STS_ERROR;
12868: x_return_status_text := 'QP_PREQ_PUB.Populate_Output_INT: '|| SQLERRM;
12869: END Populate_Output_INT;
12870: --added by yangli for Java Engine project
12871:
12872: --This is for debugging purposes
12873: PROCEDURE Populate_Output(x_return_status OUT NOCOPY VARCHAR2,
12874: x_return_status_text OUT NOCOPY VARCHAR2) IS
12875:
12876: /*
12877: INDX,QP_PREQ_PUB.populate_output.l_lines_cur,- No Index Used -,NA,NA
12878: */
12879: CURSOR l_lines_cur IS
12880: SELECT LINE_INDEX,
12881: LINE_ID,
13109: END IF;
13110: EXCEPTION
13111: WHEN OTHERS THEN
13112: IF l_debug = FND_API.G_TRUE THEN
13113: QP_PREQ_GRP.engine_debug('QP_PREQ_PUB.Populate_Output: '|| SQLERRM);
13114: END IF;
13115: x_return_status := FND_API.G_RET_STS_ERROR;
13116: x_return_status_text := 'QP_PREQ_PUB.Populate_Output: '|| SQLERRM;
13117: END Populate_Output;
13112: IF l_debug = FND_API.G_TRUE THEN
13113: QP_PREQ_GRP.engine_debug('QP_PREQ_PUB.Populate_Output: '|| SQLERRM);
13114: END IF;
13115: x_return_status := FND_API.G_RET_STS_ERROR;
13116: x_return_status_text := 'QP_PREQ_PUB.Populate_Output: '|| SQLERRM;
13117: END Populate_Output;
13118:
13119:
13120:
13150: E_NO_LINES_TO_PRICE EXCEPTION; -- 4865787
13151:
13152: l_control_rec QP_PREQ_GRP.CONTROL_RECORD_TYPE;
13153:
13154: l_routine VARCHAR2(50) := 'Routine: QP_PREQ_PUB.PRICE_REQUEST';
13155: l_output_file VARCHAR2(240);
13156:
13157: i PLS_INTEGER;
13158: j PLS_INTEGER;
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
13207: l_mod VARCHAR2(1);
13208: --added by yangli for JAVA ENGINE 3086881
13209: l_request_id NUMBER;
13210: l_status_text VARCHAR2(240);
13211: l_cleanup_flag VARCHAR2(1) := QP_PREQ_PUB.G_YES;
13212: --added by yangli for JAVA ENGINE 3086881
13213: l_no_of_lines NUMBER; -- 4865787
13214:
13215: --added for moac
13818: END IF;
13819:
13820: END IF;
13821: IF (G_NO_ADJ_PROCESSING = G_YES) AND (G_QP_INSTALL_STATUS = 'I') THEN -- Added for 3169430
13822: QP_PREQ_PUB.process_prg(x_return_status, x_return_status_text);
13823:
13824: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
13825: RAISE PRICE_REQUEST_EXC;
13826: END IF; --x_return_status
14102: --=======START: Specific only to JAVA Engine==================
14103: IF QP_PREQ_GRP.G_DEBUG_ENGINE = FND_API.G_TRUE THEN
14104: QP_PREQ_GRP.ENGINE_DEBUG('03 Java Engine is installed');
14105: QP_PREQ_GRP.ENGINE_DEBUG('temp_table_insert_flag:' || l_control_rec.temp_table_insert_flag);
14106: QP_PREQ_GRP.ENGINE_DEBUG('QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG:' || QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG);
14107: END IF;
14108:
14109: --for OM call, need Pre-Insert Logic to fetch/format data from OE tables into
14110: --Java Engine interface tables
14113:
14114: -- IF G_REQUEST_TYPE_CODE = 'ONT' THEN
14115: --bug 3085453 handle pricing availability UI
14116: -- they pass reqtype ONT and insert adj into ldets
14117: IF QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = G_YES THEN
14118: --Preinsert Logic for OM call
14119: --to fetch out-of-phases modifiers and in-phase PRG modifiers
14120: --to fetch rltd information
14121: IF QP_PREQ_GRP.G_DEBUG_ENGINE = FND_API.G_TRUE THEN
14189:
14190: -- IF G_REQUEST_TYPE_CODE = 'ONT' THEN
14191: --bug 3085453 handle pricing availability UI
14192: -- they pass reqtype ONT and insert adj into ldets
14193: IF QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = G_YES THEN
14194: -- still need Cleanup_Adjustments logic
14195: -- and Populate_Price_Adj_Id logic
14196: -- from QP_CLEANUP_ADJUSTMENTS_PVT.Fetch_Adjustments
14197: --call cleanup of adj only for OM
14199: IF l_debug = FND_API.G_TRUE THEN
14200: QP_PREQ_GRP.engine_debug('deleting related lines');
14201: END IF;
14202: DELETE FROM qp_int_rltd_lines
14203: WHERE pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
14204: AND relationship_type_code = QP_PREQ_PUB.G_PBH_LINE
14205: AND pricing_status_text = 'INSERTED FOR CALCULATION';
14206: IF l_debug = FND_API.G_TRUE THEN
14207: QP_PREQ_GRP.engine_debug('deleted related lines '|| SQL%ROWCOUNT);
14200: QP_PREQ_GRP.engine_debug('deleting related lines');
14201: END IF;
14202: DELETE FROM qp_int_rltd_lines
14203: WHERE pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
14204: AND relationship_type_code = QP_PREQ_PUB.G_PBH_LINE
14205: AND pricing_status_text = 'INSERTED FOR CALCULATION';
14206: IF l_debug = FND_API.G_TRUE THEN
14207: QP_PREQ_GRP.engine_debug('deleted related lines '|| SQL%ROWCOUNT);
14208: END IF;
14209:
14210: IF l_control_rec.calculate_flag IN
14211: (QP_PREQ_GRP.G_SEARCH_N_CALCULATE, QP_PREQ_GRP.G_CALCULATE_ONLY)
14212: THEN
14213: l_cleanup_flag := QP_PREQ_PUB.G_YES;
14214: --added for bug 3399997 by yangli
14215: Update_Line_Status(x_return_status, x_return_status_text);
14216: IF x_return_status = FND_API.G_RET_STS_ERROR
14217: THEN
14451:
14452: --Changes for bug2961617
14453:
14454: /*
14455: execute immediate 'select '||''''|| ' Total Time in QP_PREQ_PUB(in sec) : ' ||
14456: l_time_difference||' Total redo in QP_PREQ_PUB : '|| l_redo_difference||''''||' from dual ';
14457: */
14458:
14459: IF G_QP_DEBUG = G_ENGINE_TIME_TRACE_ON THEN --3085171
14452: --Changes for bug2961617
14453:
14454: /*
14455: execute immediate 'select '||''''|| ' Total Time in QP_PREQ_PUB(in sec) : ' ||
14456: l_time_difference||' Total redo in QP_PREQ_PUB : '|| l_redo_difference||''''||' from dual ';
14457: */
14458:
14459: IF G_QP_DEBUG = G_ENGINE_TIME_TRACE_ON THEN --3085171
14460: BEGIN
14459: IF G_QP_DEBUG = G_ENGINE_TIME_TRACE_ON THEN --3085171
14460: BEGIN
14461:
14462: /*
14463: select 'Total Time in QP_PREQ_PUB(in sec) : ' ||l_time_difference ||
14464: ' Total redo in QP_PREQ_PUB : '||l_redo_difference into l_time_stats from dual ;
14465: */
14466:
14467: EXECUTE IMMEDIATE 'select '|| '''' || ' Total Time in QP_PREQ_PUB(in sec) : ' ||
14460: BEGIN
14461:
14462: /*
14463: select 'Total Time in QP_PREQ_PUB(in sec) : ' ||l_time_difference ||
14464: ' Total redo in QP_PREQ_PUB : '||l_redo_difference into l_time_stats from dual ;
14465: */
14466:
14467: EXECUTE IMMEDIATE 'select '|| '''' || ' Total Time in QP_PREQ_PUB(in sec) : ' ||
14468: l_time_difference ||' Total redo in QP_PREQ_PUB : '|| l_redo_difference || '''' ||' from dual ';
14463: select 'Total Time in QP_PREQ_PUB(in sec) : ' ||l_time_difference ||
14464: ' Total redo in QP_PREQ_PUB : '||l_redo_difference into l_time_stats from dual ;
14465: */
14466:
14467: EXECUTE IMMEDIATE 'select '|| '''' || ' Total Time in QP_PREQ_PUB(in sec) : ' ||
14468: l_time_difference ||' Total redo in QP_PREQ_PUB : '|| l_redo_difference || '''' ||' from dual ';
14469:
14470: EXCEPTION
14471:
14464: ' Total redo in QP_PREQ_PUB : '||l_redo_difference into l_time_stats from dual ;
14465: */
14466:
14467: EXECUTE IMMEDIATE 'select '|| '''' || ' Total Time in QP_PREQ_PUB(in sec) : ' ||
14468: l_time_difference ||' Total redo in QP_PREQ_PUB : '|| l_redo_difference || '''' ||' from dual ';
14469:
14470: EXCEPTION
14471:
14472: WHEN OTHERS THEN
14470: EXCEPTION
14471:
14472: WHEN OTHERS THEN
14473: IF l_debug = FND_API.G_TRUE THEN
14474: QP_PREQ_GRP.engine_debug('Failed to get time statistics in QP_PREQ_PUB');
14475: END IF;
14476: END;
14477: END IF;
14478: IF l_debug = FND_API.G_TRUE THEN
14546: QP_PREQ_GRP.engine_debug('Error occured in Debug Routine: ' || x_return_status_text);
14547: END IF;
14548: WHEN E_NO_LINES_TO_PRICE THEN -- 4865787
14549: IF l_debug = FND_API.G_TRUE THEN
14550: QP_PREQ_GRP.engine_debug('Error in QP_PREQ_PUB.Price_Request: There are no lines to price!');
14551: END IF;
14552: x_return_status := FND_API.G_RET_STS_ERROR;
14553: x_return_status_text := l_routine ||' '|| ': There are no lines to price!';
14554: WHEN OTHERS THEN
14598: IF p_cust_account_id IS NOT NULL THEN
14599:
14600: --obtain_gsa indicator_flag
14601: /*
14602: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel1,HZ_CUST_ACCOUNTS_U1,CUST_ACCOUNT_ID,1
14603: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel1,HZ_PARTIES_U1,PARTY_ID,1
14604: */
14605:
14606:
14599:
14600: --obtain_gsa indicator_flag
14601: /*
14602: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel1,HZ_CUST_ACCOUNTS_U1,CUST_ACCOUNT_ID,1
14603: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel1,HZ_PARTIES_U1,PARTY_ID,1
14604: */
14605:
14606:
14607:
14658: -- 'N' or NULL means it is not a GSA customer, then check if GSA error should be raised
14659: IF l_gsa_indicator_flag = 'N' THEN
14660:
14661: /*
14662: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_CONTEXT,1
14663: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_ATTRIBUTE,2
14664: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,LIST_HEADER_ID,3
14665: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,ACTIVE_FLAG,4
14666: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_ATTR_VALUE,5
14659: IF l_gsa_indicator_flag = 'N' THEN
14660:
14661: /*
14662: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_CONTEXT,1
14663: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_ATTRIBUTE,2
14664: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,LIST_HEADER_ID,3
14665: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,ACTIVE_FLAG,4
14666: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_ATTR_VALUE,5
14667: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
14660:
14661: /*
14662: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_CONTEXT,1
14663: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_ATTRIBUTE,2
14664: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,LIST_HEADER_ID,3
14665: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,ACTIVE_FLAG,4
14666: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_ATTR_VALUE,5
14667: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
14668: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
14661: /*
14662: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_CONTEXT,1
14663: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_ATTRIBUTE,2
14664: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,LIST_HEADER_ID,3
14665: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,ACTIVE_FLAG,4
14666: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_ATTR_VALUE,5
14667: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
14668: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
14669: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
14662: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_CONTEXT,1
14663: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_ATTRIBUTE,2
14664: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,LIST_HEADER_ID,3
14665: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,ACTIVE_FLAG,4
14666: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_ATTR_VALUE,5
14667: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
14668: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
14669: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
14670: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
14663: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_ATTRIBUTE,2
14664: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,LIST_HEADER_ID,3
14665: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,ACTIVE_FLAG,4
14666: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_ATTR_VALUE,5
14667: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
14668: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
14669: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
14670: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
14671: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
14664: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,LIST_HEADER_ID,3
14665: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,ACTIVE_FLAG,4
14666: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_ATTR_VALUE,5
14667: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
14668: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
14669: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
14670: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
14671: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
14672: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
14665: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,ACTIVE_FLAG,4
14666: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_ATTR_VALUE,5
14667: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
14668: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
14669: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
14670: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
14671: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
14672: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
14673: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
14666: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_ATTR_VALUE,5
14667: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
14668: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
14669: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
14670: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
14671: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
14672: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
14673: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
14674: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
14667: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
14668: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
14669: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
14670: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
14671: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
14672: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
14673: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
14674: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
14675: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
14668: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
14669: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
14670: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
14671: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
14672: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
14673: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
14674: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
14675: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
14676: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
14669: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
14670: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
14671: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
14672: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
14673: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
14674: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
14675: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
14676: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
14677: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICE_REQ_SOURCES_PK,SOURCE_SYSTEM_CODE,2
14670: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
14671: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
14672: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
14673: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
14674: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
14675: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
14676: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
14677: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICE_REQ_SOURCES_PK,SOURCE_SYSTEM_CODE,2
14678: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_LIST_LINES_PK,LIST_LINE_ID,1
14671: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
14672: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
14673: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
14674: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
14675: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
14676: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
14677: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICE_REQ_SOURCES_PK,SOURCE_SYSTEM_CODE,2
14678: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_LIST_LINES_PK,LIST_LINE_ID,1
14679: */
14672: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
14673: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
14674: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
14675: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
14676: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
14677: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICE_REQ_SOURCES_PK,SOURCE_SYSTEM_CODE,2
14678: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_LIST_LINES_PK,LIST_LINE_ID,1
14679: */
14680: SELECT MIN(qpll.operand) INTO l_operand
14673: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
14674: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
14675: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
14676: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
14677: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICE_REQ_SOURCES_PK,SOURCE_SYSTEM_CODE,2
14678: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_LIST_LINES_PK,LIST_LINE_ID,1
14679: */
14680: SELECT MIN(qpll.operand) INTO l_operand
14681: FROM
14674: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
14675: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
14676: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
14677: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICE_REQ_SOURCES_PK,SOURCE_SYSTEM_CODE,2
14678: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_LIST_LINES_PK,LIST_LINE_ID,1
14679: */
14680: SELECT MIN(qpll.operand) INTO l_operand
14681: FROM
14682: QP_QUALIFIERS qpq,
14743: --l_return_status_text VARCHAR2(240);
14744:
14745: PRICE_REQUEST_EXC EXCEPTION;
14746:
14747: l_routine VARCHAR2(50) := 'Routine :QP_PREQ_PUB.Price_Request ';
14748:
14749:
14750: BEGIN
14751:
14809: x_pricing_status_text OUT NOCOPY VARCHAR2
14810: )
14811: IS
14812:
14813: l_routine VARCHAR2(100) := 'Routine :QP_PREQ_PUB.get_price_for_line ';
14814: BEGIN
14815:
14816: IF l_debug = FND_API.G_TRUE THEN
14817: QP_PREQ_GRP.ENGINE_DEBUG('In : ' || l_routine);
14913: x_pricing_status_text := 'Others Exception in : ' || l_routine || ' ' || SQLERRM;
14914: END get_price_for_line;
14915:
14916:
14917: END QP_PREQ_PUB;