[Home] [Help]
1: PACKAGE BODY QP_PREQ_PUB AS
2: /* $Header: QPXPPREB.pls 120.27.12010000.6 2008/08/29 10:44:51 smbalara ship $ */
3:
4: l_debug VARCHAR2(3);
5: G_VERSION VARCHAR2(240) := '/* $Header: QPXPPREB.pls 120.27.12010000.6 2008/08/29 10:44:51 smbalara 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;
2018: --l_pricing_status_code QP_PREQ_GRP.VARCHAR_TYPE;
2019: --l_pricing_status_text QP_PREQ_GRP.VARCHAR_TYPE;
2020:
2021: l_debug VARCHAR2(3);
2022: l_pbh_adj_exists VARCHAR2(1) := QP_PREQ_PUB.G_NO;
2023: l_prg_adj_exists VARCHAR2(1) := QP_PREQ_PUB.G_NO;
2024:
2025: BEGIN
2026: x_return_status := FND_API.G_RET_STS_SUCCESS;
2019: --l_pricing_status_text QP_PREQ_GRP.VARCHAR_TYPE;
2020:
2021: l_debug VARCHAR2(3);
2022: l_pbh_adj_exists VARCHAR2(1) := QP_PREQ_PUB.G_NO;
2023: l_prg_adj_exists VARCHAR2(1) := QP_PREQ_PUB.G_NO;
2024:
2025: BEGIN
2026: x_return_status := FND_API.G_RET_STS_SUCCESS;
2027: l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
2026: x_return_status := FND_API.G_RET_STS_SUCCESS;
2027: l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
2028:
2029: --per bug3238607, no need to check HVOP
2030: IF(QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES ) THEN
2031: --QP_UTIL_PUB.HVOP_PRICING_ON = QP_PREQ_PUB.G_NO) THEN
2032: IF QP_PREQ_GRP.G_DEBUG_ENGINE = FND_API.G_TRUE THEN
2033: QP_PREQ_GRP.ENGINE_DEBUG('QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG=Y and ');
2034: --'QP_BULK_PREQ_GRP.G_HVOP_Pricing_ON=N');
2027: l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
2028:
2029: --per bug3238607, no need to check HVOP
2030: IF(QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES ) THEN
2031: --QP_UTIL_PUB.HVOP_PRICING_ON = QP_PREQ_PUB.G_NO) THEN
2032: IF QP_PREQ_GRP.G_DEBUG_ENGINE = FND_API.G_TRUE THEN
2033: QP_PREQ_GRP.ENGINE_DEBUG('QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG=Y and ');
2034: --'QP_BULK_PREQ_GRP.G_HVOP_Pricing_ON=N');
2035: QP_PREQ_GRP.ENGINE_DEBUG('QP_PREQ_PUB.G_GET_FREIGHT_FLAG:' || QP_PREQ_PUB.G_GET_FREIGHT_FLAG);
2029: --per bug3238607, no need to check HVOP
2030: IF(QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES ) THEN
2031: --QP_UTIL_PUB.HVOP_PRICING_ON = QP_PREQ_PUB.G_NO) THEN
2032: IF QP_PREQ_GRP.G_DEBUG_ENGINE = FND_API.G_TRUE THEN
2033: QP_PREQ_GRP.ENGINE_DEBUG('QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG=Y and ');
2034: --'QP_BULK_PREQ_GRP.G_HVOP_Pricing_ON=N');
2035: QP_PREQ_GRP.ENGINE_DEBUG('QP_PREQ_PUB.G_GET_FREIGHT_FLAG:' || QP_PREQ_PUB.G_GET_FREIGHT_FLAG);
2036: END IF;
2037:
2031: --QP_UTIL_PUB.HVOP_PRICING_ON = QP_PREQ_PUB.G_NO) THEN
2032: IF QP_PREQ_GRP.G_DEBUG_ENGINE = FND_API.G_TRUE THEN
2033: QP_PREQ_GRP.ENGINE_DEBUG('QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG=Y and ');
2034: --'QP_BULK_PREQ_GRP.G_HVOP_Pricing_ON=N');
2035: QP_PREQ_GRP.ENGINE_DEBUG('QP_PREQ_PUB.G_GET_FREIGHT_FLAG:' || QP_PREQ_PUB.G_GET_FREIGHT_FLAG);
2036: END IF;
2037:
2038: OPEN l_ldets_cur;
2039: LOOP
2344: QP_PREQ_GRP.engine_debug('Check l_pbh_adj_exists '|| l_pbh_adj_exists);
2345: QP_PREQ_GRP.engine_debug('Check l_prg_adj_exists '|| l_prg_adj_exists);
2346: END IF;
2347:
2348: --IF l_pbh_adj_exists = QP_PREQ_PUB.G_YES
2349: --THEN
2350: -- OPEN l_rltd_lines_cur;
2351: OPEN l_rltd_lines_cur(l_pbh_adj_exists, l_prg_adj_exists);
2352: LOOP
2410: , l_LIST_LINE_ID
2411: , l_RLTD_LIST_LINE_ID
2412: , l_pricing_status_text);
2413: /* delete from qp_npreq_rltd_lines_tmp
2414: where pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
2415: and relationship_type_code = QP_PREQ_PUB.G_PBH_LINE
2416: and pricing_status_text = G_CALC_INSERT;
2417: */ --need to delete them later
2418: END LOOP;
2411: , l_RLTD_LIST_LINE_ID
2412: , l_pricing_status_text);
2413: /* delete from qp_npreq_rltd_lines_tmp
2414: where pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
2415: and relationship_type_code = QP_PREQ_PUB.G_PBH_LINE
2416: and pricing_status_text = G_CALC_INSERT;
2417: */ --need to delete them later
2418: END LOOP;
2419: CLOSE l_rltd_lines_cur;
2717: END IF;
2718:
2719: END IF;
2720:
2721: --END IF;--l_pbh_adj_exists = QP_PREQ_PUB.G_YES
2722: END IF; --(QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES AND
2723: --QP_BULK_PREQ_GRP.G_HVOP_Pricing_ON = QP_PREQ_PUB.G_NO)
2724: END;
2725:
2718:
2719: END IF;
2720:
2721: --END IF;--l_pbh_adj_exists = QP_PREQ_PUB.G_YES
2722: END IF; --(QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES AND
2723: --QP_BULK_PREQ_GRP.G_HVOP_Pricing_ON = QP_PREQ_PUB.G_NO)
2724: END;
2725:
2726: --This procedure is to update the passed in overridden pbh modifiers
2719: END IF;
2720:
2721: --END IF;--l_pbh_adj_exists = QP_PREQ_PUB.G_YES
2722: END IF; --(QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = QP_PREQ_PUB.G_YES AND
2723: --QP_BULK_PREQ_GRP.G_HVOP_Pricing_ON = QP_PREQ_PUB.G_NO)
2724: END;
2725:
2726: --This procedure is to update the passed in overridden pbh modifiers
2727: --with the setup_value_from/to, qualifier_value, price_break_type_code
2794: QP_PREQ_GRP.engine_debug('Updating the child detail type code');
2795: END IF;
2796:
2797: UPDATE qp_npreq_ldets_tmp
2798: SET line_detail_type_code = QP_PREQ_PUB.G_CHILD_DETAIL_TYPE
2799: WHERE line_detail_index IN (SELECT related_line_detail_index
2800: FROM qp_npreq_rltd_lines_tmp
2801: WHERE relationship_type_code = G_PBH_LINE
2802: AND pricing_status_code = G_STATUS_NEW);
2803:
2804: IF (l_debug = FND_API.G_TRUE) THEN
2805: FOR i IN (SELECT line_detail_index
2806: FROM qp_npreq_ldets_tmp
2807: WHERE line_detail_type_code = QP_PREQ_PUB.G_CHILD_DETAIL_TYPE)
2808: LOOP
2809: QP_PREQ_GRP.engine_debug(' PBH Child Line Detail Index : ' || i.line_detail_index);
2810: END LOOP;
2811:
2832: AND gsa_attr.attribute = G_GSA_ATTRIBUTE
2833: AND gsa_attr.value_from = G_YES;
2834:
2835: l_gsa_indicator VARCHAR2(1) := 'N';
2836: l_routine VARCHAR2(50) := 'QP_PREQ_PUB.CHECK_GSA_INDICATOR';
2837: BEGIN
2838: IF l_debug = FND_API.G_TRUE THEN
2839: QP_PREQ_GRP.engine_debug('Begin CHECK_GSA_INDICATOR '|| l_gsa_indicator);
2840:
3050:
3051: EXCEPTION
3052: WHEN OTHERS THEN
3053: IF l_debug = FND_API.G_TRUE THEN
3054: QP_PREQ_GRP.ENGINE_DEBUG('exception QP_PREQ_PUB.check gsa_violation: '|| SQLERRM);
3055: END IF;
3056: x_return_status := FND_API.G_RET_STS_ERROR;
3057: x_return_status_text := 'QP_PREQ_PUB.check gsa_violation: '|| SQLERRM;
3058: END CHECK_GSA_VIOLATION;
3053: IF l_debug = FND_API.G_TRUE THEN
3054: QP_PREQ_GRP.ENGINE_DEBUG('exception QP_PREQ_PUB.check gsa_violation: '|| SQLERRM);
3055: END IF;
3056: x_return_status := FND_API.G_RET_STS_ERROR;
3057: x_return_status_text := 'QP_PREQ_PUB.check gsa_violation: '|| SQLERRM;
3058: END CHECK_GSA_VIOLATION;
3059:
3060:
3061: PROCEDURE BACK_CALCULATION(p_line_index IN VARCHAR2
3536: IF l_debug = FND_API.G_TRUE THEN
3537: QP_PREQ_GRP.ENGINE_DEBUG('back cal fail set status');
3538: END IF;
3539: x_return_status := G_BACK_CALCULATION_STS;
3540: x_return_status_text := 'QP_PREQ_PUB: BACK CALCULATION FAILURE ';
3541: x_back_calc_rec := l_back_calc_rec;
3542: ELSE
3543: IF l_debug = FND_API.G_TRUE THEN
3544: QP_PREQ_GRP.ENGINE_DEBUG('back cal error set status');
3543: IF l_debug = FND_API.G_TRUE THEN
3544: QP_PREQ_GRP.ENGINE_DEBUG('back cal error set status');
3545: END IF;
3546: x_return_status := G_BACK_CALCULATION_STS;
3547: x_return_status_text := 'QP_PREQ_PUB: BACK CALCULATION ERROR '|| l_return_status_text;
3548: x_back_calc_rec := l_back_calc_rec;
3549: END IF;
3550: END IF;
3551:
3556: x_return_status := G_BACK_CALCULATION_STS;
3557: IF l_debug = FND_API.G_TRUE THEN
3558: QP_PREQ_GRP.ENGINE_DEBUG('BACK CALCULATION ROUTINE6');
3559: END IF;
3560: x_return_status_text := 'QP_PREQ_PUB.BACK_CAL: NO MANUAL ADJ';
3561: IF l_debug = FND_API.G_TRUE THEN
3562: QP_PREQ_GRP.ENGINE_DEBUG('BACK CALCULATION ROUTINE6.5');
3563: END IF;
3564: END IF;
3571: IF l_debug = FND_API.G_TRUE THEN
3572: QP_PREQ_GRP.ENGINE_DEBUG('BACK CALCULATION EXCEPTION '|| SQLERRM);
3573: END IF;
3574: x_return_status := FND_API.G_RET_STS_ERROR;
3575: x_return_status_text := 'Error QP_PREQ_PUB.BACK_CALCULATION '|| SQLERRM;
3576: END BACK_CALCULATION;
3577:
3578: --procedure to update the line's adjusted_unit_price with unit_price
3579: --if the engine is called with calculate_only and there are no adjustments
3654: l_amount_changed_tbl QP_PREQ_GRP.NUMBER_TYPE;
3655: l_line_unit_price_tbl QP_PREQ_GRP.NUMBER_TYPE;
3656: l_pricing_sts_code_tbl QP_PREQ_GRP.VARCHAR_TYPE;
3657: l_pricing_sts_text_tbl QP_PREQ_GRP.VARCHAR_TYPE;
3658: l_routine VARCHAR2(100) := 'QP_PREQ_PUB.UPDATE_UNIT_PRICE';
3659:
3660: l_return_status VARCHAR2(30);
3661: l_return_status_text VARCHAR2(240);
3662: l_ord_qty_operand NUMBER;
4082: --for bug 2812738 not to update if back_calc_error/gsa_violatn
4083: WHERE line.pricing_status_code = G_STATUS_UNCHANGED
4084: --fix for bug 3425569 where frozen lines were set to status updated
4085: --and processing constraints cause error in OM when they update frozen lines
4086: AND (line.price_flag IN (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
4087: OR nvl(line.processed_code, '0') = QP_PREQ_PUB.G_BY_ENGINE)
4088: AND (G_CALCULATE_FLAG = G_CALCULATE_ONLY
4089:
4090: OR EXISTS (SELECT 'Y' FROM qp_npreq_ldets_tmp ldet
4083: WHERE line.pricing_status_code = G_STATUS_UNCHANGED
4084: --fix for bug 3425569 where frozen lines were set to status updated
4085: --and processing constraints cause error in OM when they update frozen lines
4086: AND (line.price_flag IN (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
4087: OR nvl(line.processed_code, '0') = QP_PREQ_PUB.G_BY_ENGINE)
4088: AND (G_CALCULATE_FLAG = G_CALCULATE_ONLY
4089:
4090: OR EXISTS (SELECT 'Y' FROM qp_npreq_ldets_tmp ldet
4091: WHERE ldet.line_index = line.line_index
4124: --for bug 2812738 not to update if back_calc_error/gsa_violatn
4125: WHERE line.pricing_status_code = G_STATUS_UNCHANGED
4126: --fix for bug 3425569 where frozen lines were set to status updated
4127: --and processing constraints cause error in OM when they update frozen lines
4128: AND (line.price_flag IN (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
4129: OR nvl(line.processed_code, '0') = QP_PREQ_PUB.G_BY_ENGINE)
4130: AND (G_CALCULATE_FLAG = G_CALCULATE_ONLY
4131:
4132: OR EXISTS (SELECT 'Y' FROM qp_int_ldets ldet
4125: WHERE line.pricing_status_code = G_STATUS_UNCHANGED
4126: --fix for bug 3425569 where frozen lines were set to status updated
4127: --and processing constraints cause error in OM when they update frozen lines
4128: AND (line.price_flag IN (QP_PREQ_PUB.G_YES, QP_PREQ_PUB.G_PHASE, QP_PREQ_PUB.G_CALCULATE_ONLY)
4129: OR nvl(line.processed_code, '0') = QP_PREQ_PUB.G_BY_ENGINE)
4130: AND (G_CALCULATE_FLAG = G_CALCULATE_ONLY
4131:
4132: OR EXISTS (SELECT 'Y' FROM qp_int_ldets ldet
4133: WHERE ldet.line_index = line.line_index
4165:
4166: EXCEPTION
4167: WHEN OTHERS THEN
4168: x_return_status := FND_API.G_RET_STS_ERROR;
4169: x_return_status_text := 'Exception in QP_PREQ_PUB.Update_Line_Status: '|| SQLERRM;
4170: END Update_Line_Status;
4171:
4172: --overloaded for QP.G reqts
4173: --this is used in performance code path called from QPXVCLNB.fetch_adjustments
4175: PROCEDURE CALCULATE_PRICE(p_request_type_code IN VARCHAR2,
4176: p_rounding_flag IN VARCHAR2,
4177: p_view_name IN VARCHAR2,
4178: p_event_code IN VARCHAR2,
4179: p_adj_tbl IN QP_PREQ_PUB.ADJ_TBL_TYPE,
4180: x_return_status OUT NOCOPY VARCHAR2,
4181: x_return_status_text OUT NOCOPY VARCHAR2) IS
4182:
4183: --fix for bug 2515762 to print in debug the rltd info
4229: l_return_adjustment NUMBER := 0;
4230: l_sign NUMBER := 1;
4231: l_return_status VARCHAR2(30);
4232: l_return_status_text VARCHAR2(240);
4233: l_routine VARCHAR2(50) := 'Routine :QP_PREQ_PUB.Calculate_price ';
4234: --l_processed_flag VARCHAR2(1);
4235: --l_pbh_request_qty NUMBER :=0;
4236:
4237: --l_pbh_net_adj_amount NUMBER := 0; -- 2892848 no need
4242: x PLS_INTEGER := 0; -- 3126019
4243: y PLS_INTEGER;
4244: l_tbl_index PLS_INTEGER;
4245:
4246: l_adj_tbl QP_PREQ_PUB.adj_tbl_type;
4247: l_frt_tbl FRT_CHARGE_TBL;
4248: l_back_calc_ret_rec back_calc_rec_type;
4249:
4250: l_ldet_line_dtl_index_tbl QP_PREQ_GRP.NUMBER_TYPE;
4394: AND qla.attribute = qpa.product_attribute
4395: AND qla.value_from = qpa.product_attr_value
4396: AND qla.line_index = ql.line_index
4397: AND ql.price_flag <> G_PHASE
4398: AND ql.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED,
4399: QP_PREQ_PUB.G_STATUS_GSA_VIOLATION,
4400: QP_PREQ_PUB.G_STATUS_UNCHANGED)
4401: AND NOT EXISTS (SELECT qla2.line_index
4402: FROM qp_preq_line_attrs_tmp qla2, qp_pricing_attributes qpa2
4395: AND qla.value_from = qpa.product_attr_value
4396: AND qla.line_index = ql.line_index
4397: AND ql.price_flag <> G_PHASE
4398: AND ql.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED,
4399: QP_PREQ_PUB.G_STATUS_GSA_VIOLATION,
4400: QP_PREQ_PUB.G_STATUS_UNCHANGED)
4401: AND NOT EXISTS (SELECT qla2.line_index
4402: FROM qp_preq_line_attrs_tmp qla2, qp_pricing_attributes qpa2
4403: WHERE qpa2.list_line_id = p_list_line_id
4396: AND qla.line_index = ql.line_index
4397: AND ql.price_flag <> G_PHASE
4398: AND ql.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED,
4399: QP_PREQ_PUB.G_STATUS_GSA_VIOLATION,
4400: QP_PREQ_PUB.G_STATUS_UNCHANGED)
4401: AND NOT EXISTS (SELECT qla2.line_index
4402: FROM qp_preq_line_attrs_tmp qla2, qp_pricing_attributes qpa2
4403: WHERE qpa2.list_line_id = p_list_line_id
4404: AND qpa2.excluder_flag = G_YES
7096:
7097: END CALCULATE_PRICE;
7098:
7099: --this is used in pl/sql code path for OKC/OKS/ASO
7100: --this is called from QP_PREQ_PUB.price_request
7101:
7102: PROCEDURE CALCULATE_PRICE(p_rounding_flag IN VARCHAR2,
7103: x_return_status OUT NOCOPY VARCHAR2,
7104: x_return_status_text OUT NOCOPY VARCHAR2) IS
7103: x_return_status OUT NOCOPY VARCHAR2,
7104: x_return_status_text OUT NOCOPY VARCHAR2) IS
7105:
7106: /*
7107: INDX,QP_PREQ_PUB.calculate_price.l_line_cur,qp_npreq_lines_tmp_N2,LINE_TYPE_CODE,1
7108: */
7109: -- for bug 3820859, to start with adjusted_unit_price should be same as unit_price
7110: -- if not, then the same discount is getting applied twice if limit profile is set, because discounts calculations happens twice -
7111: -- once from QP_PREQ_GRP and then from QP_PREQ_PUB
7107: INDX,QP_PREQ_PUB.calculate_price.l_line_cur,qp_npreq_lines_tmp_N2,LINE_TYPE_CODE,1
7108: */
7109: -- for bug 3820859, to start with adjusted_unit_price should be same as unit_price
7110: -- if not, then the same discount is getting applied twice if limit profile is set, because discounts calculations happens twice -
7111: -- once from QP_PREQ_GRP and then from QP_PREQ_PUB
7112: CURSOR l_line_cur IS SELECT
7113: line.line_index
7114: , line.unit_price adjusted_unit_price -- bug 3820859
7115: , line.unit_price
7129: AND line.usage_pricing_type IN
7130: (QP_PREQ_GRP.G_BILLING_TYPE, QP_PREQ_GRP.G_REGULAR_USAGE_TYPE);
7131:
7132: /*
7133: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
7134: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_lines_tmp_N1,LINE_TYPE_CODE,2
7135: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_lines_tmp_N2,LINE_TYPE_CODE,1
7136: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
7137: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,2
7130: (QP_PREQ_GRP.G_BILLING_TYPE, QP_PREQ_GRP.G_REGULAR_USAGE_TYPE);
7131:
7132: /*
7133: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
7134: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_lines_tmp_N1,LINE_TYPE_CODE,2
7135: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_lines_tmp_N2,LINE_TYPE_CODE,1
7136: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
7137: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,2
7138: */
7131:
7132: /*
7133: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
7134: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_lines_tmp_N1,LINE_TYPE_CODE,2
7135: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_lines_tmp_N2,LINE_TYPE_CODE,1
7136: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
7137: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,2
7138: */
7139: CURSOR l_bucket_price_cur(p_line_index NUMBER) IS -- 2892848
7132: /*
7133: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
7134: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_lines_tmp_N1,LINE_TYPE_CODE,2
7135: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_lines_tmp_N2,LINE_TYPE_CODE,1
7136: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
7137: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,2
7138: */
7139: CURSOR l_bucket_price_cur(p_line_index NUMBER) IS -- 2892848
7140: SELECT ldet.created_from_list_line_id
7133: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_lines_tmp_N1,LINE_INDEX,1
7134: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_lines_tmp_N1,LINE_TYPE_CODE,2
7135: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_lines_tmp_N2,LINE_TYPE_CODE,1
7136: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
7137: INDX,QP_PREQ_PUB.calculate_price.l_bucket_price_cur,qp_npreq_ldets_tmp_N3,CREATED_FROM_LIST_LINE_TYPE,2
7138: */
7139: CURSOR l_bucket_price_cur(p_line_index NUMBER) IS -- 2892848
7140: SELECT ldet.created_from_list_line_id
7141: , line.line_index line_ind
7390: -- end 2892848
7391:
7392: l_return_status VARCHAR2(30);
7393: l_return_status_text VARCHAR2(240);
7394: l_routine VARCHAR2(50) := 'Routine :QP_PREQ_PUB.Calculate_price ';
7395:
7396: -- [julin/3265308] net amount calculation 'P', match product only.
7397: -- given a line line id, find the product attribute and context for
7398: -- the modifier and match all request lines with that context/attr
7406: AND qla.attribute = qpa.product_attribute
7407: AND qla.value_from = qpa.product_attr_value
7408: AND qla.line_index = ql.line_index
7409: AND ql.price_flag <> G_PHASE
7410: AND ql.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED,
7411: QP_PREQ_PUB.G_STATUS_GSA_VIOLATION,
7412: QP_PREQ_PUB.G_STATUS_UNCHANGED)
7413: AND NOT EXISTS (SELECT qla2.line_index
7414: FROM qp_preq_line_attrs_tmp qla2, qp_pricing_attributes qpa2
7407: AND qla.value_from = qpa.product_attr_value
7408: AND qla.line_index = ql.line_index
7409: AND ql.price_flag <> G_PHASE
7410: AND ql.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED,
7411: QP_PREQ_PUB.G_STATUS_GSA_VIOLATION,
7412: QP_PREQ_PUB.G_STATUS_UNCHANGED)
7413: AND NOT EXISTS (SELECT qla2.line_index
7414: FROM qp_preq_line_attrs_tmp qla2, qp_pricing_attributes qpa2
7415: WHERE qpa2.list_line_id = p_list_line_id
7408: AND qla.line_index = ql.line_index
7409: AND ql.price_flag <> G_PHASE
7410: AND ql.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED,
7411: QP_PREQ_PUB.G_STATUS_GSA_VIOLATION,
7412: QP_PREQ_PUB.G_STATUS_UNCHANGED)
7413: AND NOT EXISTS (SELECT qla2.line_index
7414: FROM qp_preq_line_attrs_tmp qla2, qp_pricing_attributes qpa2
7415: WHERE qpa2.list_line_id = p_list_line_id
7416: AND qpa2.excluder_flag = G_YES
8719: ||' count '|| l_ldet_tbl.COUNT);
8720: END IF;
8721:
8722: /*
8723: INDX,QP_PREQ_PUB.calculate_price.upd1,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
8724: */
8725: UPDATE qp_npreq_ldets_tmp SET
8726: adjustment_amount = l_ldet_tbl(x).adjustment_amount,
8727: applied_flag = l_ldet_tbl(x).applied_flag,
8931:
8932: END IF;
8933:
8934: /*
8935: INDX,QP_PREQ_PUB.calculate_price.upd2,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
8936: */
8937: UPDATE qp_npreq_ldets_tmp ldet
8938: SET ldet.operand_value =
8939: l_back_calc_ret_rec.operand_value
9005: QP_PREQ_GRP.ENGINE_DEBUG('Back calculate Failure');
9006: END IF;
9007: l_lines_tbl(j).processed_flag := G_NOT_PROCESSED;
9008: l_lines_tbl(j).processed_code :=
9009: QP_PREQ_PUB.G_BACK_CALCULATION_STS;
9010: l_lines_tbl(j).pricing_status_code :=
9011: QP_PREQ_PUB.G_BACK_CALCULATION_STS;
9012: l_lines_tbl(j).pricing_status_text := l_return_status_text;
9013: END IF; --l_return_status
9007: l_lines_tbl(j).processed_flag := G_NOT_PROCESSED;
9008: l_lines_tbl(j).processed_code :=
9009: QP_PREQ_PUB.G_BACK_CALCULATION_STS;
9010: l_lines_tbl(j).pricing_status_code :=
9011: QP_PREQ_PUB.G_BACK_CALCULATION_STS;
9012: l_lines_tbl(j).pricing_status_text := l_return_status_text;
9013: END IF; --l_return_status
9014: ELSE
9015: IF l_debug = FND_API.G_TRUE THEN
9020:
9021:
9022: IF l_lines_tbl(j).processed_flag = G_PROCESSED THEN
9023: /*
9024: INDX,QP_PREQ_PUB.calculate_price.upd3,qp_npreq_lines_tmp_N1,LINE_INDEX,1
9025: */
9026: UPDATE qp_npreq_lines_tmp SET adjusted_unit_price
9027: = nvl(l_lines_tbl(j).updated_adjusted_unit_price,
9028: l_lines_tbl(j).adjusted_unit_price)
9045: 'processed_code' || l_lines_tbl(j).processed_code);
9046: END IF;
9047: ELSE
9048: /*
9049: INDX,QP_PREQ_PUB.calculate_price.upd4,qp_npreq_lines_tmp_N1,LINE_INDEX,1
9050: */
9051: UPDATE qp_npreq_lines_tmp SET adjusted_unit_price
9052: = l_lines_tbl(j).adjusted_unit_price
9053: --, adjusted_unit_price_ur = l_adjusted_unit_price_ur(j) --[prarasto:Post Round] added unrounded adjusted unit price, [julin/postround] redesign
9106: QP_PREQ_GRP.engine_debug('Begin Pub GSA check');
9107:
9108: END IF;
9109: /*
9110: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_lines_tmp_N2,LINE_TYPE_CODE,1
9111: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N2,LINE_TYPE_CODE,1
9112: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N1,LINE_INDEX,1
9113: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N1,ATTRIBUTE_TYPE,2
9114: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
9107:
9108: END IF;
9109: /*
9110: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_lines_tmp_N2,LINE_TYPE_CODE,1
9111: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N2,LINE_TYPE_CODE,1
9112: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N1,LINE_INDEX,1
9113: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N1,ATTRIBUTE_TYPE,2
9114: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
9115: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
9108: END IF;
9109: /*
9110: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_lines_tmp_N2,LINE_TYPE_CODE,1
9111: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N2,LINE_TYPE_CODE,1
9112: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N1,LINE_INDEX,1
9113: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N1,ATTRIBUTE_TYPE,2
9114: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
9115: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
9116: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
9109: /*
9110: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_lines_tmp_N2,LINE_TYPE_CODE,1
9111: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N2,LINE_TYPE_CODE,1
9112: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N1,LINE_INDEX,1
9113: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N1,ATTRIBUTE_TYPE,2
9114: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
9115: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
9116: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
9117: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
9110: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_lines_tmp_N2,LINE_TYPE_CODE,1
9111: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N2,LINE_TYPE_CODE,1
9112: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N1,LINE_INDEX,1
9113: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N1,ATTRIBUTE_TYPE,2
9114: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
9115: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
9116: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
9117: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
9118: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_VALUE,5
9111: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N2,LINE_TYPE_CODE,1
9112: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N1,LINE_INDEX,1
9113: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N1,ATTRIBUTE_TYPE,2
9114: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
9115: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
9116: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
9117: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
9118: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_VALUE,5
9119: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
9112: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N1,LINE_INDEX,1
9113: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N1,ATTRIBUTE_TYPE,2
9114: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
9115: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
9116: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
9117: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
9118: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_VALUE,5
9119: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
9120: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
9113: INDX,QP_PREQ_PUB.calculate_price.upd5,qp_npreq_line_attrs_tmp_N1,ATTRIBUTE_TYPE,2
9114: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
9115: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
9116: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
9117: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
9118: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_VALUE,5
9119: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
9120: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
9121: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
9114: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
9115: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
9116: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
9117: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
9118: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_VALUE,5
9119: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
9120: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
9121: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
9122: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
9115: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
9116: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
9117: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
9118: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_VALUE,5
9119: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
9120: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
9121: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
9122: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
9123: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,ACTIVE_FLAG,1
9116: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
9117: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
9118: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_VALUE,5
9119: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
9120: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
9121: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
9122: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
9123: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,ACTIVE_FLAG,1
9124: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,CURRENCY_CODE,2
9117: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
9118: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_VALUE,5
9119: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
9120: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
9121: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
9122: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
9123: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,ACTIVE_FLAG,1
9124: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,CURRENCY_CODE,2
9125: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,LIST_TYPE_CODE,3
9118: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_VALUE,5
9119: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
9120: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
9121: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
9122: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
9123: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,ACTIVE_FLAG,1
9124: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,CURRENCY_CODE,2
9125: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,LIST_TYPE_CODE,3
9126: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,SOURCE_SYSTEM_CODE,4
9119: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
9120: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
9121: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
9122: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
9123: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,ACTIVE_FLAG,1
9124: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,CURRENCY_CODE,2
9125: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,LIST_TYPE_CODE,3
9126: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,SOURCE_SYSTEM_CODE,4
9127: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,LIST_HEADER_ID,5
9120: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
9121: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
9122: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
9123: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,ACTIVE_FLAG,1
9124: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,CURRENCY_CODE,2
9125: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,LIST_TYPE_CODE,3
9126: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,SOURCE_SYSTEM_CODE,4
9127: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,LIST_HEADER_ID,5
9128: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,GSA_INDICATOR,6
9121: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
9122: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
9123: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,ACTIVE_FLAG,1
9124: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,CURRENCY_CODE,2
9125: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,LIST_TYPE_CODE,3
9126: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,SOURCE_SYSTEM_CODE,4
9127: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,LIST_HEADER_ID,5
9128: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,GSA_INDICATOR,6
9129: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_LINES_PK,LIST_LINE_ID,1
9122: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
9123: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,ACTIVE_FLAG,1
9124: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,CURRENCY_CODE,2
9125: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,LIST_TYPE_CODE,3
9126: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,SOURCE_SYSTEM_CODE,4
9127: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,LIST_HEADER_ID,5
9128: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,GSA_INDICATOR,6
9129: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_LINES_PK,LIST_LINE_ID,1
9130: */
9123: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,ACTIVE_FLAG,1
9124: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,CURRENCY_CODE,2
9125: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,LIST_TYPE_CODE,3
9126: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,SOURCE_SYSTEM_CODE,4
9127: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,LIST_HEADER_ID,5
9128: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,GSA_INDICATOR,6
9129: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_LINES_PK,LIST_LINE_ID,1
9130: */
9131: IF l_debug = FND_API.G_TRUE THEN
9124: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,CURRENCY_CODE,2
9125: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,LIST_TYPE_CODE,3
9126: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,SOURCE_SYSTEM_CODE,4
9127: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,LIST_HEADER_ID,5
9128: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,GSA_INDICATOR,6
9129: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_LINES_PK,LIST_LINE_ID,1
9130: */
9131: IF l_debug = FND_API.G_TRUE THEN
9132: QP_PREQ_GRP.engine_debug('Pub GSA attr_mgr_installed '
9125: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,LIST_TYPE_CODE,3
9126: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,SOURCE_SYSTEM_CODE,4
9127: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,LIST_HEADER_ID,5
9128: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_HEADERS_B_N1,GSA_INDICATOR,6
9129: INDX,QP_PREQ_PUB.calculate_price.upd5,QP_LIST_LINES_PK,LIST_LINE_ID,1
9130: */
9131: IF l_debug = FND_API.G_TRUE THEN
9132: QP_PREQ_GRP.engine_debug('Pub GSA attr_mgr_installed '
9133: || G_ATTR_MGR_INSTALLED);
9135: IF G_ATTR_MGR_INSTALLED = G_NO
9136: THEN
9137: UPDATE qp_npreq_lines_tmp line
9138: SET line.pricing_status_code = G_STATUS_GSA_VIOLATION
9139: , line.pricing_status_text = 'QP_PREQ_PUB: GSA VIOLATION'
9140: WHERE line.line_type_code = G_LINE_LEVEL
9141: --made this change look at only successful lines
9142: AND line.pricing_status_code IN
9143: (G_STATUS_UPDATED, G_STATUS_UNCHANGED)
9189: , line.pricing_effective_date)));
9190: ELSE --G_ATTR_MGR_INSTALLED
9191: UPDATE qp_npreq_lines_tmp line
9192: SET line.pricing_status_code = G_STATUS_GSA_VIOLATION
9193: , line.pricing_status_text = 'QP_PREQ_PUB: GSA VIOLATION'
9194: WHERE line.line_type_code = G_LINE_LEVEL
9195: --made this change look at only successful lines
9196: AND line.pricing_status_code IN
9197: (G_STATUS_UPDATED, G_STATUS_UNCHANGED)
9335: END IF;
9336: EXCEPTION
9337: WHEN OTHERS THEN
9338: x_return_status := FND_API.G_RET_STS_ERROR;
9339: x_return_status_text := 'Exception in QP_PREQ_PUB.Cleanup_rltd_lines '|| SQLERRM;
9340: END Cleanup_rltd_lines;
9341:
9342:
9343: --coded for bug 2264566
9482: END IF;
9483: EXCEPTION
9484: WHEN OTHERS THEN
9485: x_return_status := FND_API.G_RET_STS_ERROR;
9486: x_return_status_text := 'Exception in QP_PREQ_PUB.Update_Related_Line_Info '|| SQLERRM;
9487: END Update_Related_Line_Info;
9488:
9489:
9490: --This is to cleanup duplicate adjustments between user passed
9493: , X_RETURN_STATUS OUT NOCOPY VARCHAR2
9494: , X_RETURN_STATUS_TEXT OUT NOCOPY VARCHAR2) IS
9495:
9496: /*
9497: INDX,QP_PREQ_PUB.process_adjustments.l_duplicate_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
9498: INDX,QP_PREQ_PUB.process_adjustments.l_duplicate_cur,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
9499: */
9500: CURSOR l_duplicate_cur IS
9501: SELECT ldeta.line_detail_index
9494: , X_RETURN_STATUS_TEXT OUT NOCOPY VARCHAR2) IS
9495:
9496: /*
9497: INDX,QP_PREQ_PUB.process_adjustments.l_duplicate_cur,qp_npreq_ldets_tmp_N1,LINE_INDEX,1
9498: INDX,QP_PREQ_PUB.process_adjustments.l_duplicate_cur,qp_npreq_ldets_tmp_N1,CREATED_FROM_LIST_LINE_ID,5
9499: */
9500: CURSOR l_duplicate_cur IS
9501: SELECT ldeta.line_detail_index
9502: , ldeta.line_index
9528:
9529: TYPE DUP_ADJ_TBL_TYPE IS TABLE OF l_duplicate_rec%TYPE INDEX BY BINARY_INTEGER;
9530:
9531: l_dup_adj_tbl DUP_ADJ_TBL_TYPE;
9532: l_routine VARCHAR2(100) := 'Routine:QP_PREQ_PUB.PROCESS_ADJUSTMENTS';
9533:
9534: /*
9535: indxno index used
9536: */
9544:
9545: lrec lcur%ROWTYPE;
9546:
9547: /*
9548: INDX,QP_PREQ_PUB.process_adjustments.lcur1,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
9549: INDX,QP_PREQ_PUB.process_adjustments.lcur1,qp_npreq_ldets_tmp_N3,PRICING_STATUS_CODE,4
9550: */
9551: CURSOR lcur1 IS
9552: SELECT created_from_list_line_id
9545: lrec lcur%ROWTYPE;
9546:
9547: /*
9548: INDX,QP_PREQ_PUB.process_adjustments.lcur1,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
9549: INDX,QP_PREQ_PUB.process_adjustments.lcur1,qp_npreq_ldets_tmp_N3,PRICING_STATUS_CODE,4
9550: */
9551: CURSOR lcur1 IS
9552: SELECT created_from_list_line_id
9553: , line_detail_index
9610: --event passed in the control record
9611:
9612:
9613: /*
9614: INDX,QP_PREQ_PUB.process_adjustments.upd1,QP_EVENT_PHASES_U1,PRICING_EVENT_CODE,1
9615: */
9616: UPDATE qp_npreq_ldets_tmp ldet SET ldet.process_code = G_STATUS_NEW
9617: WHERE ldet.pricing_status_code = G_STATUS_UNCHANGED AND
9618: ldet.applied_flag = G_YES AND
9742: END IF;
9743: l_duplicate_rec.process_code := G_STATUS_NEW;
9744: l_duplicate_rec1.process_code := G_STATUS_UNCHANGED;
9745: l_duplicate_rec1.pricing_status_code := G_STATUS_DELETED;
9746: l_duplicate_rec1.pricing_status_text := 'QP_PREQ_PUB:DUPLICATE RECORD';
9747:
9748: ELSIF (l_duplicate_rec.pricing_status_code = G_STATUS_UNCHANGED AND
9749: ((nvl(l_duplicate_rec.updated_flag, G_NO) = G_NO) OR
9750: (nvl(l_duplicate_rec.updated_flag, G_NO) = G_NO))) THEN
9753: QP_PREQ_GRP.ENGINE_DEBUG('retain engine passed');
9754: END IF;
9755: l_duplicate_rec.process_code := G_STATUS_UNCHANGED;
9756: l_duplicate_rec.pricing_status_code := G_STATUS_DELETED;
9757: l_duplicate_rec1.pricing_status_text := 'QP_PREQ_PUB:DUPLICATE RECORD';
9758: l_duplicate_rec1.process_code := G_STATUS_NEW;
9759: ELSE
9760: --retain adj passed by engine
9761: IF l_debug = FND_API.G_TRUE THEN
9763:
9764: END IF;
9765: l_duplicate_rec.process_code := G_STATUS_UNCHANGED;
9766: l_duplicate_rec.pricing_status_code := G_STATUS_DELETED;
9767: l_duplicate_rec.pricing_status_text := 'QP_PREQ_PUB:DUPLICATE RECORD';
9768: l_duplicate_rec1.process_code := G_STATUS_NEW;
9769: END IF;
9770: ELSE
9771: NULL;
9797: i := l_dup_adj_tbl.FIRST;
9798: WHILE i IS NOT NULL LOOP
9799:
9800: /*
9801: INDX,QP_PREQ_PUB.process_adjustments.upd4,qp_npreq_ldets_tmp_U1,LINE_DETAIL_INDEX,1
9802: */
9803: UPDATE qp_npreq_ldets_tmp SET
9804: process_code = l_dup_adj_tbl(i).process_code
9805: , pricing_status_code = l_dup_adj_tbl(i).pricing_status_code
9936: IF l_debug = FND_API.G_TRUE THEN
9937: QP_PREQ_GRP.engine_debug('Error UPDATE_QUALIFIER_VALUE routine '|| SQLERRM);
9938: END IF;
9939: x_return_status := FND_API.G_RET_STS_ERROR;
9940: x_return_status_text := 'QP_PREQ_PUB.UPDATE_QUALIFIER_VALUE: '|| SQLERRM;
9941: END UPDATE_QUALIFIER_VALUE;
9942:
9943: --This is to update the status on the child break lines after calculation
9944: PROCEDURE Update_Child_Break_Lines(x_return_status OUT NOCOPY VARCHAR2,
10029: IF l_debug = FND_API.G_TRUE THEN
10030: QP_PREQ_GRP.engine_debug('Error Update_Child_break_lines routine '|| SQLERRM);
10031: END IF;
10032: x_return_status := FND_API.G_RET_STS_ERROR;
10033: x_return_status_text := 'QP_PREQ_PUB.Update_Child_Break_lines: '|| SQLERRM;
10034:
10035: END Update_Child_Break_Lines;
10036:
10037:
10415: END IF;
10416: EXCEPTION
10417: WHEN OTHERS THEN
10418: x_return_status := FND_API.G_RET_STS_ERROR;
10419: x_return_status_text := 'QP_PREQ_PUB.Process_Price_Break Exception: '
10420: || SQLERRM;
10421: IF l_debug = FND_API.G_TRUE THEN
10422: QP_PREQ_GRP.engine_debug('Exception process_price_break '|| SQLERRM);
10423: END IF;
10425:
10426: PROCEDURE Update_Service_Lines(x_return_status OUT NOCOPY VARCHAR2,
10427: x_return_status_text OUT NOCOPY VARCHAR2) IS
10428: /*
10429: INDX,QP_PREQ_PUB.Usage_pricing.l_Service_Cur,-No Index Used-,NA,NA
10430: */
10431: CURSOR l_Service_Cur IS
10432: SELECT rltd.line_index,
10433: rltd.related_line_index,
10483: END IF;
10484: EXCEPTION
10485: WHEN OTHERS THEN
10486: x_return_status := FND_API.G_RET_STS_ERROR;
10487: x_return_status_text := 'QP_PREQ_PUB.Update_Service_Lines Exception: '
10488: || SQLERRM;
10489: IF l_debug = FND_API.G_TRUE THEN
10490: QP_PREQ_GRP.engine_debug('Exception Update_Service_Lines '|| SQLERRM);
10491: END IF;
10780: IF l_debug = FND_API.G_TRUE THEN
10781: QP_PREQ_GRP.engine_debug('Exception Usage Pricing '|| SQLERRM);
10782: END IF;
10783: x_return_status := FND_API.G_RET_STS_ERROR;
10784: x_return_status_text := 'QP_PREQ_PUB.Usage_Pricing Exception: '
10785: || SQLERRM;
10786: END Usage_pricing;
10787:
10788: FUNCTION Call_Usage_Pricing RETURN VARCHAR2 IS
10802: RETURN x_call_usage_pricing;
10803: EXCEPTION
10804: WHEN OTHERS THEN
10805: IF l_debug = FND_API.G_TRUE THEN
10806: QP_PREQ_GRP.engine_debug('QP_PREQ_PUB.Call_Usage_Pricing Exception: '|| SQLERRM);
10807: END IF;
10808: x_call_usage_pricing := NULL;
10809: END Call_Usage_Pricing;
10810:
10839: --bug 3085453 handle pricing availability UI
10840: -- they pass reqtype ONT and insert adj into ldets
10841: --bug 3085453 handle pricing availability UI
10842: -- they pass reqtype ONT and insert adj into ldets
10843: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = G_YES
10844: --need not do freegoods cleanup if PRG line has price_flag 'P' or 'N'
10845: AND buyline.price_flag = G_YES --in (G_YES, G_PHASE)
10846: AND instr(p_event_code, ev.pricing_event_code || ',') > 0
10847: AND ph.pricing_phase_id = ev.pricing_phase_id
10888: , qp_npreq_lines_tmp oldfreeline
10889: --where G_REQUEST_TYPE_CODE = 'ONT'
10890: --bug 3085453 handle pricing availability UI
10891: -- they pass reqtype ONT and insert adj into ldets
10892: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = G_YES
10893: --need not do freegoods cleanup if PRG line has price_flag 'P' or 'N'
10894: AND buyline.price_flag = G_YES --in (G_YES, G_PHASE)
10895: AND instr(p_event_code, ev.pricing_event_code || ',') > 0
10896: AND ph.pricing_phase_id = ev.pricing_phase_id
10938: , qp_npreq_lines_tmp oldfreeline
10939: --where G_REQUEST_TYPE_CODE <> 'ONT'
10940: --bug 3085453 handle pricing availability UI
10941: -- they pass reqtype ONT and insert adj into ldets
10942: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG <> G_YES
10943: AND buyline.price_flag = G_YES --in (G_YES, G_PHASE)
10944: AND instr(p_event_code, ev.pricing_event_code || ',') > 0
10945: AND ph.pricing_phase_id = ev.pricing_phase_id
10946: AND ((G_GET_FREIGHT_FLAG = G_YES AND ph.freight_exists = 'Y')
11003: AND prg.list_line_type_code = 'PRG'
11004: AND ass.price_adjustment_id = prg.price_adjustment_id
11005: AND fgdis.price_adjustment_id = ass.rltd_price_adj_id;
11006:
11007: l_routine VARCHAR2(100) := 'QP_PREQ_PUB.Identify_freegood_lines ';
11008: BEGIN
11009: l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
11010: IF l_debug = FND_API.G_TRUE THEN
11011: QP_PREQ_GRP.engine_debug('Begin Identify_freegood_lines: '|| p_event_code);
11041: , oe_price_adjustments oldfgdis
11042: -- where G_REQUEST_TYPE_CODE = 'ONT'
11043: --bug 3085453 handle pricing availability UI
11044: -- they pass reqtype ONT and insert adj into ldets
11045: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = G_YES
11046: AND instr(p_event_code, ev.pricing_event_code || ',') > 0
11047: AND ph.pricing_phase_id = ev.pricing_phase_id
11048: AND ((buyline.line_type_code = G_LINE_LEVEL
11049: AND oldprg.line_id = buyline.line_id)
11166: l_mark_fgdis_delete_index QP_PREQ_GRP.number_type;
11167: l_prg_list_line_id NUMBER;
11168:
11169: l_debug VARCHAR2(3);
11170: l_routine VARCHAR2(50) := 'QP_PREQ_PUB.Update_prg_pricing_status';
11171: BEGIN
11172: l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
11173: IF l_debug = FND_API.G_TRUE THEN
11174: QP_PREQ_GRP.engine_debug('Begin Update_prg_pricing_status');
11195: QP_PREQ_GRP.engine_debug('Mark delete prg list_line_id -1');
11196: END;
11197: END LOOP;
11198: END IF; --l_debug
11199: IF QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = 'N' THEN
11200: FORALL i IN l_mark_prg_delete_index.FIRST..l_mark_prg_delete_index.LAST
11201: UPDATE qp_npreq_ldets_tmp SET pricing_status_code = G_STATUS_DELETED
11202: WHERE line_detail_index = l_mark_prg_delete_index(i);
11203: END IF;
11356: , qp_npreq_line_attrs_tmp newfgitem
11357: --where G_REQUEST_TYPE_CODE = 'ONT'
11358: --bug 3085453 handle pricing availability UI
11359: -- they pass reqtype ONT and insert adj into ldets
11360: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = G_YES
11361: AND instr(oldfreeline.process_status, G_FREEGOOD) > 0
11362: AND newrltd.pricing_status_code = G_STATUS_NEW
11363: AND instr(oldfreeline.process_status
11364: , G_BUYLINE || newrltd.line_index || G_PROMO_GOODS_DISCOUNT) > 0
11476: , qp_npreq_line_attrs_tmp oldfreeitem
11477: --WHERE G_REQUEST_TYPE_CODE <> 'ONT'
11478: --bug 3085453 handle pricing availability UI
11479: -- they pass reqtype ONT and insert adj into ldets
11480: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG <> G_YES
11481: AND instr(oldfreeline.process_status, G_FREEGOOD) > 0
11482: AND newfgdis.pricing_status_code = G_STATUS_NEW
11483: AND newfgdis.applied_flag = G_YES
11484: AND instr(oldfreeline.process_status, G_FREEGOOD || newfgdis.created_from_list_line_id || G_BUYLINE) > 0
11503: qp_npreq_ldets_tmp ldet, qp_npreq_rltd_lines_tmp rltd
11504: --WHERE G_REQUEST_TYPE_CODE = 'ONT'
11505: --bug 3085453 handle pricing availability UI
11506: -- they pass reqtype ONT and insert adj into ldets
11507: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = G_YES
11508: AND buyline.line_type_code = G_LINE_LEVEL
11509: AND prg.line_id = buyline.line_id
11510: AND prg.list_line_type_code = G_PROMO_GOODS_DISCOUNT
11511: AND prg.updated_flag = G_YES
11522: qp_npreq_ldets_tmp ldet, qp_npreq_rltd_lines_tmp rltd
11523: --WHERE G_REQUEST_TYPE_CODE = 'ONT'
11524: --bug 3085453 handle pricing availability UI
11525: -- they pass reqtype ONT and insert adj into ldets
11526: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = G_YES
11527: AND buyline.line_type_code = G_ORDER_LEVEL
11528: AND prg.line_id IS NULL
11529: AND prg.header_id = buyline.line_id
11530: AND prg.list_line_type_code = G_PROMO_GOODS_DISCOUNT
11543: qp_npreq_ldets_tmp ldet, qp_npreq_rltd_lines_tmp rltd
11544: --WHERE G_REQUEST_TYPE_CODE <> 'ONT'
11545: --bug 3085453 handle pricing availability UI
11546: -- they pass reqtype ONT and insert adj into ldets
11547: WHERE QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG <> G_YES
11548: AND prg.line_index = buyline.line_index
11549: AND prg.pricing_status_code = G_STATUS_UNCHANGED
11550: AND prg.created_from_list_line_type = G_PROMO_GOODS_DISCOUNT
11551: AND prg.updated_flag = G_YES
11564:
11565: l_upd_engine_fg_index QP_PREQ_GRP.NUMBER_TYPE;
11566:
11567: l_Process_PRG VARCHAR2(1) := G_NO;
11568: l_routine VARCHAR2(100) := 'QP_PREQ_PUB.Process_PRG ';
11569: l_oldfgdis_list_line_id VARCHAR2(2000);
11570: l_old_operand VARCHAR2(240);
11571:
11572: i PLS_INTEGER;
12000: -- Bug 3348208
12001: -- the following if condition for check_cust_view_flag is commented out so that
12002: -- update_prg_pricing_status is always run. Previously, it was introduced only
12003: -- for ASO, not OM, but we discovered it needs to go for OM as well.
12004: --IF QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG <> G_YES THEN
12005: update_prg_pricing_status(x_return_status, x_return_status_text);
12006:
12007: x_return_status := FND_API.G_RET_STS_SUCCESS;
12008: x_return_status_text := NULL;
12026: --If the selling price changed, then it marks the newly created line
12027: --as UPDATED, else the passed in freegood line is marked as unchanged
12028: PROCEDURE Update_PRG_Process_status(x_return_status OUT NOCOPY VARCHAR2,
12029: x_return_status_text OUT NOCOPY VARCHAR2) IS
12030: l_routine VARCHAR2(100) := 'QP_PREQ_PUB.Update_PRG_Process_status ';
12031:
12032: BEGIN
12033: l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
12034: IF l_debug = FND_API.G_TRUE THEN
12086: --bug 2970368
12087: --IF G_REQUEST_TYPE_CODE <> 'ONT' THEN
12088: --bug 3085453 handle pricing availability UI
12089: -- they pass reqtype ONT and insert adj into ldets
12090: IF QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG <> G_YES THEN
12091: update_prg_pricing_status(x_return_status, x_return_status_text);
12092: END IF; --G_REQUEST_TYPE_CODE
12093:
12094: x_return_status := FND_API.G_RET_STS_SUCCESS;
12129: AND dis.line_detail_index = rltd.related_line_detail_index
12130: AND dis.pricing_status_code = G_STATUS_NEW;
12131:
12132: I PLS_INTEGER;
12133: l_routine VARCHAR2(100) := 'QP_PREQ_PUB.Populate_buy_line_price_flag';
12134: BEGIN
12135: IF l_debug = FND_API.G_TRUE THEN
12136: QP_PREQ_GRP.engine_debug('Begin Populate_buy_line_price_flag');
12137:
12231: p_line_index IN NUMBER)
12232: RETURN VARCHAR2 IS
12233:
12234: i PLS_INTEGER;
12235: l_routine VARCHAR2(100) := 'QP_PREQ_PUB.Get_buy_line_price_flag';
12236: l_count NUMBER;
12237: BEGIN
12238: IF l_debug = FND_API.G_TRUE THEN
12239: 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);
12328:
12329: EXCEPTION
12330: WHEN Coupon_Exception THEN
12331: IF l_debug = FND_API.G_TRUE THEN
12332: QP_PREQ_GRP.engine_debug('Exception in QP_PREQ_PUB.Process_coupon_issue '|| x_return_status_text);
12333: END IF;
12334: WHEN OTHERS THEN
12335: x_return_status := FND_API.G_RET_STS_ERROR;
12336: x_return_status_text := 'Exception in QP_PREQ_PUB.Process_coupon_issue '|| SQLERRM;
12332: QP_PREQ_GRP.engine_debug('Exception in QP_PREQ_PUB.Process_coupon_issue '|| x_return_status_text);
12333: END IF;
12334: WHEN OTHERS THEN
12335: x_return_status := FND_API.G_RET_STS_ERROR;
12336: x_return_status_text := 'Exception in QP_PREQ_PUB.Process_coupon_issue '|| SQLERRM;
12337: IF l_debug = FND_API.G_TRUE THEN
12338: QP_PREQ_GRP.engine_debug(x_return_status_text);
12339: END IF;
12340: END Process_coupon_issue;
12382: l_recur_benefit_qty_tbl QP_PREQ_GRP.number_type; --Bug 2804053
12383: l_recur_benefit_qty NUMBER;
12384: l_recur_base_qty NUMBER;
12385: l_recur_qualifying_qty NUMBER;
12386: l_routine VARCHAR2(100) := 'QP_PREQ_PUB.Update_recurring_quantity';
12387:
12388: BEGIN
12389: l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
12390: l_recur_dtl_index_tbl.DELETE;
12476: PROCEDURE Populate_Output_INT(x_return_status OUT NOCOPY VARCHAR2,
12477: x_return_status_text OUT NOCOPY VARCHAR2) IS
12478:
12479: /*
12480: INDX,QP_PREQ_PUB.populate_output_int.l_lines_cur,- No Index Used -,NA,NA
12481: */
12482: CURSOR l_lines_cur IS
12483: SELECT LINE_INDEX,
12484: LINE_ID,
12704: END IF;
12705: EXCEPTION
12706: WHEN OTHERS THEN
12707: IF l_debug = FND_API.G_TRUE THEN
12708: QP_PREQ_GRP.engine_debug('QP_PREQ_PUB.Populate_Output_INT: '|| SQLERRM);
12709: END IF;
12710: x_return_status := FND_API.G_RET_STS_ERROR;
12711: x_return_status_text := 'QP_PREQ_PUB.Populate_Output_INT: '|| SQLERRM;
12712: END Populate_Output_INT;
12707: IF l_debug = FND_API.G_TRUE THEN
12708: QP_PREQ_GRP.engine_debug('QP_PREQ_PUB.Populate_Output_INT: '|| SQLERRM);
12709: END IF;
12710: x_return_status := FND_API.G_RET_STS_ERROR;
12711: x_return_status_text := 'QP_PREQ_PUB.Populate_Output_INT: '|| SQLERRM;
12712: END Populate_Output_INT;
12713: --added by yangli for Java Engine project
12714:
12715: --This is for debugging purposes
12716: PROCEDURE Populate_Output(x_return_status OUT NOCOPY VARCHAR2,
12717: x_return_status_text OUT NOCOPY VARCHAR2) IS
12718:
12719: /*
12720: INDX,QP_PREQ_PUB.populate_output.l_lines_cur,- No Index Used -,NA,NA
12721: */
12722: CURSOR l_lines_cur IS
12723: SELECT LINE_INDEX,
12724: LINE_ID,
12944: END IF;
12945: EXCEPTION
12946: WHEN OTHERS THEN
12947: IF l_debug = FND_API.G_TRUE THEN
12948: QP_PREQ_GRP.engine_debug('QP_PREQ_PUB.Populate_Output: '|| SQLERRM);
12949: END IF;
12950: x_return_status := FND_API.G_RET_STS_ERROR;
12951: x_return_status_text := 'QP_PREQ_PUB.Populate_Output: '|| SQLERRM;
12952: END Populate_Output;
12947: IF l_debug = FND_API.G_TRUE THEN
12948: QP_PREQ_GRP.engine_debug('QP_PREQ_PUB.Populate_Output: '|| SQLERRM);
12949: END IF;
12950: x_return_status := FND_API.G_RET_STS_ERROR;
12951: x_return_status_text := 'QP_PREQ_PUB.Populate_Output: '|| SQLERRM;
12952: END Populate_Output;
12953:
12954:
12955:
12985: E_NO_LINES_TO_PRICE EXCEPTION; -- 4865787
12986:
12987: l_control_rec QP_PREQ_GRP.CONTROL_RECORD_TYPE;
12988:
12989: l_routine VARCHAR2(50) := 'Routine: QP_PREQ_PUB.PRICE_REQUEST';
12990: l_output_file VARCHAR2(240);
12991:
12992: i PLS_INTEGER;
12993: j PLS_INTEGER;
12992: i PLS_INTEGER;
12993: j PLS_INTEGER;
12994:
12995: /*
12996: INDX,QP_PREQ_PUB.price_request.lcur1,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
12997: INDX,QP_PREQ_PUB.price_request.lcur1,qp_npreq_ldets_tmp_N3,PRICING_STATUS_CODE,4
12998: */
12999: CURSOR lcur1 IS
13000: SELECT ldet.created_from_list_line_id
12993: j PLS_INTEGER;
12994:
12995: /*
12996: INDX,QP_PREQ_PUB.price_request.lcur1,qp_npreq_ldets_tmp_N3,LINE_INDEX,1
12997: INDX,QP_PREQ_PUB.price_request.lcur1,qp_npreq_ldets_tmp_N3,PRICING_STATUS_CODE,4
12998: */
12999: CURSOR lcur1 IS
13000: SELECT ldet.created_from_list_line_id
13001: , line.line_index line_ind
13042: l_mod VARCHAR2(1);
13043: --added by yangli for JAVA ENGINE 3086881
13044: l_request_id NUMBER;
13045: l_status_text VARCHAR2(240);
13046: l_cleanup_flag VARCHAR2(1) := QP_PREQ_PUB.G_YES;
13047: --added by yangli for JAVA ENGINE 3086881
13048: l_no_of_lines NUMBER; -- 4865787
13049:
13050: --added for moac
13632: END IF;
13633:
13634: END IF;
13635: IF (G_NO_ADJ_PROCESSING = G_YES) AND (G_QP_INSTALL_STATUS = 'I') THEN -- Added for 3169430
13636: QP_PREQ_PUB.process_prg(x_return_status, x_return_status_text);
13637:
13638: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
13639: RAISE PRICE_REQUEST_EXC;
13640: END IF; --x_return_status
13916: --=======START: Specific only to JAVA Engine==================
13917: IF QP_PREQ_GRP.G_DEBUG_ENGINE = FND_API.G_TRUE THEN
13918: QP_PREQ_GRP.ENGINE_DEBUG('03 Java Engine is installed');
13919: QP_PREQ_GRP.ENGINE_DEBUG('temp_table_insert_flag:' || l_control_rec.temp_table_insert_flag);
13920: QP_PREQ_GRP.ENGINE_DEBUG('QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG:' || QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG);
13921: END IF;
13922:
13923: --for OM call, need Pre-Insert Logic to fetch/format data from OE tables into
13924: --Java Engine interface tables
13927:
13928: -- IF G_REQUEST_TYPE_CODE = 'ONT' THEN
13929: --bug 3085453 handle pricing availability UI
13930: -- they pass reqtype ONT and insert adj into ldets
13931: IF QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = G_YES THEN
13932: --Preinsert Logic for OM call
13933: --to fetch out-of-phases modifiers and in-phase PRG modifiers
13934: --to fetch rltd information
13935: IF QP_PREQ_GRP.G_DEBUG_ENGINE = FND_API.G_TRUE THEN
14003:
14004: -- IF G_REQUEST_TYPE_CODE = 'ONT' THEN
14005: --bug 3085453 handle pricing availability UI
14006: -- they pass reqtype ONT and insert adj into ldets
14007: IF QP_PREQ_PUB.G_CHECK_CUST_VIEW_FLAG = G_YES THEN
14008: -- still need Cleanup_Adjustments logic
14009: -- and Populate_Price_Adj_Id logic
14010: -- from QP_CLEANUP_ADJUSTMENTS_PVT.Fetch_Adjustments
14011: --call cleanup of adj only for OM
14013: IF l_debug = FND_API.G_TRUE THEN
14014: QP_PREQ_GRP.engine_debug('deleting related lines');
14015: END IF;
14016: DELETE FROM qp_int_rltd_lines
14017: WHERE pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
14018: AND relationship_type_code = QP_PREQ_PUB.G_PBH_LINE
14019: AND pricing_status_text = 'INSERTED FOR CALCULATION';
14020: IF l_debug = FND_API.G_TRUE THEN
14021: QP_PREQ_GRP.engine_debug('deleted related lines '|| SQL%ROWCOUNT);
14014: QP_PREQ_GRP.engine_debug('deleting related lines');
14015: END IF;
14016: DELETE FROM qp_int_rltd_lines
14017: WHERE pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
14018: AND relationship_type_code = QP_PREQ_PUB.G_PBH_LINE
14019: AND pricing_status_text = 'INSERTED FOR CALCULATION';
14020: IF l_debug = FND_API.G_TRUE THEN
14021: QP_PREQ_GRP.engine_debug('deleted related lines '|| SQL%ROWCOUNT);
14022: END IF;
14023:
14024: IF l_control_rec.calculate_flag IN
14025: (QP_PREQ_GRP.G_SEARCH_N_CALCULATE, QP_PREQ_GRP.G_CALCULATE_ONLY)
14026: THEN
14027: l_cleanup_flag := QP_PREQ_PUB.G_YES;
14028: --added for bug 3399997 by yangli
14029: Update_Line_Status(x_return_status, x_return_status_text);
14030: IF x_return_status = FND_API.G_RET_STS_ERROR
14031: THEN
14265:
14266: --Changes for bug2961617
14267:
14268: /*
14269: execute immediate 'select '||''''|| ' Total Time in QP_PREQ_PUB(in sec) : ' ||
14270: l_time_difference||' Total redo in QP_PREQ_PUB : '|| l_redo_difference||''''||' from dual ';
14271: */
14272:
14273: IF G_QP_DEBUG = G_ENGINE_TIME_TRACE_ON THEN --3085171
14266: --Changes for bug2961617
14267:
14268: /*
14269: execute immediate 'select '||''''|| ' Total Time in QP_PREQ_PUB(in sec) : ' ||
14270: l_time_difference||' Total redo in QP_PREQ_PUB : '|| l_redo_difference||''''||' from dual ';
14271: */
14272:
14273: IF G_QP_DEBUG = G_ENGINE_TIME_TRACE_ON THEN --3085171
14274: BEGIN
14273: IF G_QP_DEBUG = G_ENGINE_TIME_TRACE_ON THEN --3085171
14274: BEGIN
14275:
14276: /*
14277: select 'Total Time in QP_PREQ_PUB(in sec) : ' ||l_time_difference ||
14278: ' Total redo in QP_PREQ_PUB : '||l_redo_difference into l_time_stats from dual ;
14279: */
14280:
14281: EXECUTE IMMEDIATE 'select '|| '''' || ' Total Time in QP_PREQ_PUB(in sec) : ' ||
14274: BEGIN
14275:
14276: /*
14277: select 'Total Time in QP_PREQ_PUB(in sec) : ' ||l_time_difference ||
14278: ' Total redo in QP_PREQ_PUB : '||l_redo_difference into l_time_stats from dual ;
14279: */
14280:
14281: EXECUTE IMMEDIATE 'select '|| '''' || ' Total Time in QP_PREQ_PUB(in sec) : ' ||
14282: l_time_difference ||' Total redo in QP_PREQ_PUB : '|| l_redo_difference || '''' ||' from dual ';
14277: select 'Total Time in QP_PREQ_PUB(in sec) : ' ||l_time_difference ||
14278: ' Total redo in QP_PREQ_PUB : '||l_redo_difference into l_time_stats from dual ;
14279: */
14280:
14281: EXECUTE IMMEDIATE 'select '|| '''' || ' Total Time in QP_PREQ_PUB(in sec) : ' ||
14282: l_time_difference ||' Total redo in QP_PREQ_PUB : '|| l_redo_difference || '''' ||' from dual ';
14283:
14284: EXCEPTION
14285:
14278: ' Total redo in QP_PREQ_PUB : '||l_redo_difference into l_time_stats from dual ;
14279: */
14280:
14281: EXECUTE IMMEDIATE 'select '|| '''' || ' Total Time in QP_PREQ_PUB(in sec) : ' ||
14282: l_time_difference ||' Total redo in QP_PREQ_PUB : '|| l_redo_difference || '''' ||' from dual ';
14283:
14284: EXCEPTION
14285:
14286: WHEN OTHERS THEN
14284: EXCEPTION
14285:
14286: WHEN OTHERS THEN
14287: IF l_debug = FND_API.G_TRUE THEN
14288: QP_PREQ_GRP.engine_debug('Failed to get time statistics in QP_PREQ_PUB');
14289: END IF;
14290: END;
14291: END IF;
14292: IF l_debug = FND_API.G_TRUE THEN
14358: QP_PREQ_GRP.engine_debug('Error occured in Debug Routine: ' || x_return_status_text);
14359: END IF;
14360: WHEN E_NO_LINES_TO_PRICE THEN -- 4865787
14361: IF l_debug = FND_API.G_TRUE THEN
14362: QP_PREQ_GRP.engine_debug('Error in QP_PREQ_PUB.Price_Request: There are no lines to price!');
14363: END IF;
14364: x_return_status := FND_API.G_RET_STS_ERROR;
14365: x_return_status_text := l_routine ||' '|| ': There are no lines to price!';
14366: WHEN OTHERS THEN
14410: IF p_cust_account_id IS NOT NULL THEN
14411:
14412: --obtain_gsa indicator_flag
14413: /*
14414: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel1,HZ_CUST_ACCOUNTS_U1,CUST_ACCOUNT_ID,1
14415: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel1,HZ_PARTIES_U1,PARTY_ID,1
14416: */
14417:
14418:
14411:
14412: --obtain_gsa indicator_flag
14413: /*
14414: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel1,HZ_CUST_ACCOUNTS_U1,CUST_ACCOUNT_ID,1
14415: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel1,HZ_PARTIES_U1,PARTY_ID,1
14416: */
14417:
14418:
14419:
14470: -- 'N' or NULL means it is not a GSA customer, then check if GSA error should be raised
14471: IF l_gsa_indicator_flag = 'N' THEN
14472:
14473: /*
14474: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_CONTEXT,1
14475: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_ATTRIBUTE,2
14476: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,LIST_HEADER_ID,3
14477: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,ACTIVE_FLAG,4
14478: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_ATTR_VALUE,5
14471: IF l_gsa_indicator_flag = 'N' THEN
14472:
14473: /*
14474: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_CONTEXT,1
14475: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_ATTRIBUTE,2
14476: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,LIST_HEADER_ID,3
14477: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,ACTIVE_FLAG,4
14478: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_ATTR_VALUE,5
14479: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
14472:
14473: /*
14474: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_CONTEXT,1
14475: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_ATTRIBUTE,2
14476: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,LIST_HEADER_ID,3
14477: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,ACTIVE_FLAG,4
14478: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_ATTR_VALUE,5
14479: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
14480: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
14473: /*
14474: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_CONTEXT,1
14475: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_ATTRIBUTE,2
14476: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,LIST_HEADER_ID,3
14477: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,ACTIVE_FLAG,4
14478: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_ATTR_VALUE,5
14479: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
14480: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
14481: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
14474: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_CONTEXT,1
14475: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_ATTRIBUTE,2
14476: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,LIST_HEADER_ID,3
14477: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,ACTIVE_FLAG,4
14478: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_ATTR_VALUE,5
14479: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
14480: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
14481: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
14482: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
14475: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_ATTRIBUTE,2
14476: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,LIST_HEADER_ID,3
14477: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,ACTIVE_FLAG,4
14478: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_ATTR_VALUE,5
14479: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
14480: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
14481: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
14482: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
14483: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
14476: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,LIST_HEADER_ID,3
14477: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,ACTIVE_FLAG,4
14478: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_ATTR_VALUE,5
14479: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
14480: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
14481: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
14482: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
14483: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
14484: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
14477: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,ACTIVE_FLAG,4
14478: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_ATTR_VALUE,5
14479: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
14480: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
14481: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
14482: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
14483: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
14484: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
14485: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
14478: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_QUALIFIERS_N7,QUALIFIER_ATTR_VALUE,5
14479: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
14480: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
14481: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
14482: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
14483: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
14484: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
14485: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
14486: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
14479: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRICING_PHASE_ID,1
14480: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
14481: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
14482: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
14483: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
14484: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
14485: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
14486: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
14487: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
14480: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,QUALIFICATION_IND,2
14481: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
14482: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
14483: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
14484: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
14485: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
14486: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
14487: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
14488: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
14481: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE_CONTEXT,3
14482: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
14483: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
14484: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
14485: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
14486: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
14487: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
14488: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
14489: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICE_REQ_SOURCES_PK,SOURCE_SYSTEM_CODE,2
14482: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTRIBUTE,4
14483: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
14484: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
14485: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
14486: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
14487: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
14488: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
14489: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICE_REQ_SOURCES_PK,SOURCE_SYSTEM_CODE,2
14490: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_LIST_LINES_PK,LIST_LINE_ID,1
14483: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,PRODUCT_ATTR_VALUE,5
14484: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
14485: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
14486: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
14487: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
14488: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
14489: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICE_REQ_SOURCES_PK,SOURCE_SYSTEM_CODE,2
14490: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_LIST_LINES_PK,LIST_LINE_ID,1
14491: */
14484: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,EXCLUDER_FLAG,6
14485: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
14486: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
14487: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
14488: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
14489: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICE_REQ_SOURCES_PK,SOURCE_SYSTEM_CODE,2
14490: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_LIST_LINES_PK,LIST_LINE_ID,1
14491: */
14492: SELECT MIN(qpll.operand) INTO l_operand
14485: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_HEADER_ID,7
14486: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
14487: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
14488: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
14489: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICE_REQ_SOURCES_PK,SOURCE_SYSTEM_CODE,2
14490: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_LIST_LINES_PK,LIST_LINE_ID,1
14491: */
14492: SELECT MIN(qpll.operand) INTO l_operand
14493: FROM
14486: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICING_ATTRIBUTES_N5,LIST_LINE_ID,8
14487: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_LIST_HEADERS_B_PK,LIST_HEADER_ID,1
14488: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICE_REQ_SOURCES_PK,REQUEST_TYPE_CODE,1
14489: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_PRICE_REQ_SOURCES_PK,SOURCE_SYSTEM_CODE,2
14490: INDX,QP_PREQ_PUB.Raise_GSA_Error.sel2,QP_LIST_LINES_PK,LIST_LINE_ID,1
14491: */
14492: SELECT MIN(qpll.operand) INTO l_operand
14493: FROM
14494: QP_QUALIFIERS qpq,
14555: --l_return_status_text VARCHAR2(240);
14556:
14557: PRICE_REQUEST_EXC EXCEPTION;
14558:
14559: l_routine VARCHAR2(50) := 'Routine :QP_PREQ_PUB.Price_Request ';
14560:
14561:
14562: BEGIN
14563:
14621: x_pricing_status_text OUT NOCOPY VARCHAR2
14622: )
14623: IS
14624:
14625: l_routine VARCHAR2(100) := 'Routine :QP_PREQ_PUB.get_price_for_line ';
14626: BEGIN
14627:
14628: IF l_debug = FND_API.G_TRUE THEN
14629: QP_PREQ_GRP.ENGINE_DEBUG('In : ' || l_routine);
14725: x_pricing_status_text := 'Others Exception in : ' || l_routine || ' ' || SQLERRM;
14726: END get_price_for_line;
14727:
14728:
14729: END QP_PREQ_PUB;