[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(continuous_price_break_flag, 'N')
FROM qp_list_lines
WHERE list_line_id = p_list_line_id;
SELECT 'Y' INTO l_prorated_flag
FROM qp_npreq_ldets_tmp
WHERE line_detail_index = p_list_line_index
AND break_uom_code IS NOT NULL;
SELECT line_index, attribute_type, context, attribute, value_from, grouping_number
FROM qp_npreq_line_attrs_tmp
WHERE list_line_id = p_list_line_id
AND pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW;
SELECT accum_context, accum_attribute, accum_attr_run_src_flag, list_line_no
INTO l_accum_context, l_accum_attrib, l_accum_flag, v_list_line_no
FROM qp_list_lines
WHERE list_line_id = p_list_line_id;
SELECT DISTINCT value_from
INTO l_accum
FROM qp_npreq_line_attrs_tmp
WHERE line_index = l_line_index -- (l_line_index is a global package variable)
AND context = l_accum_context
AND attribute = l_accum_attrib
AND pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED;
SELECT line_id
INTO QP_PREQ_GRP.G_ORDER_ID
FROM qp_npreq_lines_tmp
WHERE line_type_code = QP_PREQ_GRP.G_ORDER_LEVEL;
QP_PREQ_GRP.engine_debug(' ^ G_ORDER_ID was null, select statement got value '|| QP_PREQ_GRP.G_ORDER_ID);
SELECT lines.line_id, lines.pricing_effective_date
INTO v_line_id, v_price_eff_date
FROM qp_npreq_lines_tmp lines
WHERE lines.line_index = l_line_index;
SELECT 1 INTO l_valid_line_id
FROM oe_order_lines_all
WHERE line_id=v_line_id
AND rownum=1;
SELECT rltd.related_line_detail_index
, rltd.list_line_id -- for accum range break
, rltd.related_list_line_id
, rltd.related_list_line_type
, rltd.operand_calculation_code
, rltd.operand
, rltd.pricing_group_sequence
, rltd.relationship_type_detail price_break_type
, rltd.setup_value_from value_from
, rltd.setup_value_to value_to
, rltd.qualifier_value
, line.line_quantity ordered_qty
, line.priced_quantity priced_qty
, line.catchweight_qty catchweight_qty
, line.actual_order_quantity actual_order_qty
, line.PRICE_REQUEST_CODE PRICE_REQUEST_CODE --bug#12916970
FROM qp_npreq_rltd_lines_tmp rltd, qp_npreq_lines_tmp line
WHERE rltd.list_line_id = p_list_line_id
AND rltd.line_index = p_line_index
AND line.line_index = p_line_index
AND rltd.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
AND rltd.line_index = line.line_index -- sql repos
UNION
SELECT rltd.related_line_detail_index
, rltd.list_line_id -- for accum range break
, rltd.related_list_line_id
, rltd.related_list_line_type
, rltd.operand_calculation_code
, rltd.operand
, rltd.pricing_group_sequence
, rltd.relationship_type_detail price_break_type
, rltd.setup_value_from value_from
, rltd.setup_value_to value_to
, rltd.qualifier_value
, line.line_quantity ordered_qty
, line.priced_quantity priced_qty
, line.catchweight_qty catchweight_qty
, line.actual_order_quantity actual_order_qty
, line.PRICE_REQUEST_CODE PRICE_REQUEST_CODE -- bug#12916970
FROM qp_npreq_rltd_lines_tmp rltd, qp_npreq_lines_tmp line
WHERE QP_PREQ_GRP.G_PUBLIC_API_CALL_FLAG = QP_PREQ_GRP.G_YES
AND rltd.line_index = p_line_index
AND line_detail_index = p_list_line_id
AND line.line_index = p_line_index
AND rltd.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
AND rltd.line_index = line.line_index -- sql repos
ORDER BY 7, 9;
select qpltr.amount
from qp_limit_transactions qpltr
where qpltr.PRICE_REQUEST_CODE = l_PRICE_REQUEST_CODE;
SELECT nvl(qpll.continuous_price_break_flag, 'N')
FROM qp_list_lines qpll, qp_npreq_rltd_lines_tmp rltd
WHERE rltd.line_index = p_line_index
AND rltd.line_detail_index = p_list_line_id
AND rltd.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
AND rltd.list_line_id = qpll.list_line_id
AND ROWNUM = 1;
UPDATE qp_npreq_ldets_tmp
--changes for bug 2264566 update adjamt on child lines of manualpbh
SET adjustment_amount = - (pbh_adjustments(i).l_adjustment_amount),
--SET adjustment_amount = l_sign * pbh_adjustments(i).l_adjustment_amount,
line_quantity = nvl(pbh_adjustments(i).l_raw_satisfied_value, -- 5335689
pbh_adjustments(i).l_satisfied_value)
, order_qty_adj_amt = - (l_ord_qty_adj_amt) -- bug 3285662
, order_qty_operand = l_ord_qty_operand
WHERE line_detail_index = pbh_adjustments(i).l_related_line_detail_index
AND pricing_status_code IN (QP_PREQ_GRP.G_STATUS_NEW,
--changes for bug 2264566 update adjamt on child lines of manualpbh
QP_PREQ_GRP.G_STATUS_UNCHANGED);
QP_Resolve_Incompatability_PVT.Delete_Ldets_Complete(
l_del_index_tbl, 'No breaks qualified', x_return_status, x_return_status_text);
SELECT related_line_detail_index
, related_list_line_id
, related_list_line_type
, list_line_id -- for accum range break
, operand_calculation_code
, operand
, pricing_group_sequence
, relationship_type_detail price_break_type
, setup_value_from value_from
, setup_value_to value_to
, qualifier_value
FROM qp_npreq_rltd_lines_tmp
WHERE QP_PREQ_GRP.G_PUBLIC_API_CALL_FLAG = QP_PREQ_GRP.G_NO
AND list_line_id = p_list_line_id
AND line_index = p_line_index
AND pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
--fix for bug 2515762 added the union clause to distinguish between
--the passed-in rltd lines of PBH modifier versus the engine-inserted
--when called from public API
--made corresponding changes in the public API to pass the dtl_index
UNION
SELECT related_line_detail_index
, related_list_line_id
, related_list_line_type
, list_line_id -- for accum range break
, operand_calculation_code
, operand
, pricing_group_sequence
, relationship_type_detail price_break_type
, setup_value_from value_from
, setup_value_to value_to
, qualifier_value
FROM qp_npreq_rltd_lines_tmp
WHERE QP_PREQ_GRP.G_PUBLIC_API_CALL_FLAG = QP_PREQ_GRP.G_YES
AND line_index = p_line_index
AND line_detail_index = p_list_line_id
AND pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
ORDER BY 7 ;
SELECT nvl(continuous_price_break_flag, 'N')
FROM qp_list_lines
WHERE QP_PREQ_GRP.G_PUBLIC_API_CALL_FLAG = QP_PREQ_GRP.G_NO
AND list_line_id = p_list_line_id
AND ROWNUM = 1
UNION
SELECT nvl(qpll.continuous_price_break_flag, 'N')
FROM qp_list_lines qpll, qp_npreq_rltd_lines_tmp rltd
WHERE QP_PREQ_GRP.G_PUBLIC_API_CALL_FLAG = QP_PREQ_GRP.G_YES
AND rltd.line_index = p_line_index
AND rltd.line_detail_index = p_list_line_id
AND rltd.list_line_id = qpll.list_line_id
AND ROWNUM = 1;
UPDATE qp_npreq_ldets_tmp
--changes for bug 2264566 update adjamt on child lines of manualpbh
SET adjustment_amount = - (pbh_adjustments(i).l_adjustment_amount),
--SET adjustment_amount = l_sign * pbh_adjustments(i).l_adjustment_amount,
line_quantity = pbh_adjustments(i).l_satisfied_value
WHERE line_detail_index = pbh_adjustments(i).l_related_line_detail_index
AND pricing_status_code IN (QP_PREQ_GRP.G_STATUS_NEW,
--changes for bug 2264566 update adjamt on child lines of manualpbh
QP_PREQ_GRP.G_STATUS_UNCHANGED);
SELECT b.SOURCE_SYSTEM_CODE
FROM qp_npreq_ldets_tmp a, QP_LIST_HEADERS_B b
WHERE a.CREATED_FROM_LIST_HEADER_ID = b.LIST_HEADER_ID
AND a.LINE_INDEX = p_line_index
AND a.CREATED_FROM_LIST_LINE_TYPE = 'PLL'
AND a.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
SELECT /*+ ORDERED USE_NL(a b c d e) */ c.LIST_LINE_ID, c.OPERAND
FROM
qp_npreq_line_attrs_tmp a, QP_PRICING_ATTRIBUTES b, QP_LIST_LINES c, QP_LIST_HEADERS_B d, QP_QUALIFIERS e
WHERE a.LINE_INDEX = p_line_index
AND a.CONTEXT = b.PRODUCT_ATTRIBUTE_CONTEXT
AND a.ATTRIBUTE = b.PRODUCT_ATTRIBUTE
AND a.VALUE_FROM = b.PRODUCT_ATTR_VALUE
AND b.PRICING_ATTRIBUTE_CONTEXT IS NULL
AND b.LIST_LINE_ID = c.LIST_LINE_ID
AND c.ARITHMETIC_OPERATOR = QP_PREQ_GRP.G_NEWPRICE_DISCOUNT
AND c.LIST_HEADER_ID = d.LIST_HEADER_ID
AND trunc(p_pricing_date) BETWEEN nvl(TRUNC(c.START_DATE_ACTIVE), trunc(p_pricing_date))
AND nvl(TRUNC(c.END_DATE_ACTIVE), trunc(p_pricing_date))
AND d.SOURCE_SYSTEM_CODE = p_source_system_code
AND d.ACTIVE_FLAG = QP_PREQ_GRP.G_YES
AND d.LIST_HEADER_ID = e.LIST_HEADER_ID
AND e.QUALIFIER_CONTEXT = QP_PREQ_GRP.G_CUSTOMER_CONTEXT
AND e.QUALIFIER_ATTRIBUTE = QP_PREQ_GRP.G_GSA_ATTRIBUTE
AND e.QUALIFIER_ATTR_VALUE = QP_PREQ_GRP.G_YES
ORDER BY 2;
QP_PREQ_GRP.engine_debug ('Before Update List Price after rounding1: ' || p_request_line.UNIT_PRICE);