[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT qph.list_header_id,qph.name,qpl.list_line_id,qpl.list_line_type_code,
qpl.operand, qpl.arithmetic_operator,qph.discount_lines_flag
FROM QP_LIST_HEADERS qph , QP_LIST_LINES qpl,QP_QUALIFIERS qpq
WHERE qph.LIST_HEADER_ID = qpl.LIST_HEADER_ID
AND qpl.LIST_LINE_TYPE_CODE IN ('DIS','PBH')
AND qph.LIST_HEADER_ID = qpq.LIST_HEADER_ID
AND qph.AUTOMATIC_FLAG = 'Y'
AND ((p_gsa = 'Y') OR nvl(qph.GSA_INDICATOR , 'N') = 'N')
AND qpq.QUALIFIER_CONTEXT = p_context
AND qpq.QUALIFIER_ATTRIBUTE = p_attribute
AND qpq.QUALIFIER_ATTR_VALUE = p_attr_value
AND trunc(p_pricing_date) BETWEEN nvl(qpl.START_DATE_ACTIVE,trunc(p_pricing_date))
AND nvl(qpl.END_DATE_ACTIVE,trunc(p_pricing_date))
AND ( not exists (select null
from qp_qualifiers qpq
where qpq.list_header_id = qph.list_header_id
and qpq.qualifier_context = p_customer_context
and qpq.qualifier_attribute in (p_customer_class_attr , p_sold_to_org_attr, p_site_org_attr))
or
( exists(select null
from qp_qualifiers qpq
where qpq.list_header_id = qph.list_header_id
and (qpq.qualifier_context = p_customer_context
and qpq.qualifier_attribute = p_sold_to_org_attr
and nvl(qpq.qualifier_attr_value,p_sold_to_org_id) = p_sold_to_org_id))
or exists(select null
from qp_qualifiers qpq
where qpq.list_header_id = qph.list_header_id
and (qpq.qualifier_context = p_customer_context
and qpq.qualifier_attribute = p_customer_class_attr
and nvl(qpq.qualifier_attr_value,p_customer_class_code) = p_customer_class_code))
and exists(select null
from qp_qualifiers qpq
where (qpq.qualifier_context = p_customer_context
and qpq.qualifier_attribute = p_site_org_attr
and (nvl(qpq.qualifier_attr_value,p_ship_to_id) = p_ship_to_id
or nvl(qpq.qualifier_attr_value,p_invoice_to_id) = p_invoice_to_id)))));
SELECT 'X'
FROM QP_LIST_LINES qpl, QP_PRICING_ATTRIBUTES qpbl
WHERE qpl.LIST_LINE_ID = p_list_line_id
AND qpl.LIST_LINE_ID = qpbl.LIST_LINE_ID
AND qpbl.PRODUCT_ATTRIBUTE_CONTEXT = p_product_attribute_context
AND qpbl.PRODUCT_ATTRIBUTE = p_product_attribute
AND qpbl.PRODUCT_ATTR_VALUE = p_attribute_value
AND nvl(qpbl.PRODUCT_UOM_CODE,nvl(p_unit_code,'NULL')) = nvl(p_unit_code,'NULL')
AND trunc(p_pricing_date) BETWEEN nvl(qpl.START_DATE_ACTIVE,trunc(p_pricing_date))
AND nvl(qpl.END_DATE_ACTIVE,trunc(p_pricing_date))
UNION
SELECT 'Y'
FROM QP_LIST_LINES qpl, QP_QUALIFIERS qpq
WHERE qpl.LIST_LINE_ID = p_list_line_id
AND qpl.LIST_LINE_ID = qpq.LIST_LINE_ID
AND qpq.QUALIFIER_CONTEXT = p_qualifier_context
AND qpq.QUALIFIER_ATTRIBUTE = p_qualifier_attribute
AND qpq.QUALIFIER_ATTR_VALUE = p_attribute_value
AND trunc(p_pricing_date) BETWEEN nvl(qpl.START_DATE_ACTIVE,trunc(p_pricing_date))
AND nvl(qpl.END_DATE_ACTIVE,trunc(p_pricing_date));
SELECT qpbl.PRICING_ATTRIBUTE_ID,qpbl.LIST_LINE_ID,qpbl.PRICING_ATTRIBUTE_CONTEXT,qpbl.PRICING_ATTRIBUTE,
qpbl.PRICING_ATTR_VALUE_FROM, qpbl.PRICING_ATTR_VALUE_TO,qpl.OPERAND , qpl.ARITHMETIC_OPERATOR
FROM QP_PRICING_ATTRIBUTES qpbl , QP_RLTD_MODIFIERS qprl, QP_LIST_LINES qpl
WHERE qpl.LIST_LINE_ID = qpbl.LIST_LINE_ID
AND qpbl.LIST_LINE_ID = qprl.TO_RLTD_MODIFIER_ID
AND qpbl.PRODUCT_ATTRIBUTE_CONTEXT = p_product_attribute_context
AND qpbl.PRODUCT_ATTRIBUTE = p_product_attribute
AND qpbl.PRODUCT_ATTR_VALUE = p_attribute_value
AND nvl(qpbl.PRODUCT_UOM_CODE,nvl(p_unit_code,'NULL')) = nvl(p_unit_code,'NULL')
AND qprl.FROM_RLTD_MODIFIER_ID = p_list_line_id
AND trunc(p_pricing_date) BETWEEN nvl(qpl.START_DATE_ACTIVE,trunc(p_pricing_date))
AND nvl(qpl.END_DATE_ACTIVE,trunc(p_pricing_date))
UNION
SELECT qpbl.PRICING_ATTRIBUTE_ID,qpbl.LIST_LINE_ID,qpbl.PRICING_ATTRIBUTE_CONTEXT,qpbl.PRICING_ATTRIBUTE,
qpbl.PRICING_ATTR_VALUE_FROM, qpbl.PRICING_ATTR_VALUE_TO,qpl.OPERAND , qpl.ARITHMETIC_OPERATOR
FROM QP_PRICING_ATTRIBUTES qpbl , QP_LIST_LINES qpl, QP_QUALIFIERS qpq, QP_RLTD_MODIFIERS qprl
WHERE qpq.LIST_LINE_ID = p_list_line_id
AND qpq.QUALIFIER_CONTEXT = p_qualifier_context
AND qpq.QUALIFIER_ATTRIBUTE = p_qualifier_attribute
AND qpq.QUALIFIER_ATTR_VALUE = p_attribute_value
AND qpbl.LIST_LINE_ID = qprl.TO_RLTD_MODIFIER_ID
AND qpl.LIST_LINE_ID = qpbl.LIST_LINE_ID
AND qpl.LIST_LINE_ID IN ( SELECT TO_RLTD_MODIFIER_ID
FROM QP_RLTD_MODIFIERS qprl
WHERE qprl.FROM_RLTD_MODIFIER_ID = p_list_line_id)
AND trunc(p_pricing_date) BETWEEN nvl(qpl.START_DATE_ACTIVE,trunc(p_pricing_date))
AND nvl(qpl.END_DATE_ACTIVE,trunc(p_pricing_date));
SELECT 'x'
INTO v_dummy
FROM QP_RLTD_MODIFIERS
WHERE TO_RLTD_MODIFIER_ID = i.list_line_id;
-- This cursor will take care of both product and qualifier contexts. Only 1 Select in the UNION
-- will be successful any time depending on whether it is a product or qualifier
FOR j in get_price_break_lines
(i.list_line_id,v_product_context,v_product_attribute,v_qualifier_context,v_qualifier_attribute)
LOOP
--dbms_output.put_line('Inside the Price break Loop');
--Delete all the records
DELETE FROM QP_DISCOUNTS_UPG_TEMP;
INSERT INTO QP_DISCOUNTS_UPG_TEMP VALUES(v_discount_lines_tbl(v_index).p_discount_id,
v_discount_lines_tbl(v_index).p_discount_name,
v_discount_lines_tbl(v_index).p_discount_line_id,
v_discount_lines_tbl(v_index).p_discount_percent);
SELECT discount_id , discount_name , discount_line_id, discount_percent
INTO x_discount_rec.p_discount_id,
x_discount_rec.p_discount_name,
x_discount_rec.p_discount_line_id,
x_discount_rec.p_discount_percent
FROM QP_DISCOUNTS_UPG_TEMP
WHERE DISCOUNT_PERCENT = (SELECT min(DISCOUNT_PERCENT) FROM QP_DISCOUNTS_UPG_TEMP)
AND ROWNUM = 1;
SELECT discount_id , discount_name , discount_line_id, discount_percent
INTO x_discount_rec.p_discount_id,
x_discount_rec.p_discount_name,
x_discount_rec.p_discount_line_id,
x_discount_rec.p_discount_percent
FROM QP_DISCOUNTS_UPG_TEMP
WHERE DISCOUNT_PERCENT = (SELECT max(DISCOUNT_PERCENT) FROM QP_DISCOUNTS_UPG_TEMP)
AND ROWNUM = 1;