The following lines contain the word 'select', 'insert', 'update' or 'delete':
ELSIF l_PRICE_LIST_rec.operation = OE_GLOBALS.G_OPR_UPDATE
OR l_PRICE_LIST_rec.operation = OE_GLOBALS.G_OPR_DELETE
THEN
l_PRICE_LIST_rec.db_flag := FND_API.G_TRUE;
IF l_PRICE_LIST_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
OE_Validate_Price_List.Entity_Delete
( x_return_status => l_return_status
, p_PRICE_LIST_rec => l_PRICE_LIST_rec
);
IF l_PRICE_LIST_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
OE_Price_List_Util.Delete_Row
( p_name => l_PRICE_LIST_rec.name
, p_price_list_id => l_PRICE_LIST_rec.price_list_id
);
l_PRICE_LIST_rec.last_update_date := SYSDATE;
l_PRICE_LIST_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_PRICE_LIST_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
IF l_PRICE_LIST_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
OE_Price_List_Util.Update_Row (l_PRICE_LIST_rec);
OE_Price_List_Util.Insert_Row (l_PRICE_LIST_rec);
ELSIF l_PRICE_LIST_LINE_rec.operation = OE_GLOBALS.G_OPR_UPDATE
OR l_PRICE_LIST_LINE_rec.operation = OE_GLOBALS.G_OPR_DELETE
THEN
l_PRICE_LIST_LINE_rec.db_flag := FND_API.G_TRUE;
IF l_PRICE_LIST_LINE_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
OE_Validate_Price_List_Line.Entity_Delete
( x_return_status => l_return_status
, p_PRICE_LIST_LINE_rec => l_PRICE_LIST_LINE_rec
);
IF l_PRICE_LIST_LINE_rec.operation = OE_GLOBALS.G_OPR_DELETE THEN
OE_Price_List_Line_Util.Delete_Row
( p_price_list_line_id => l_PRICE_LIST_LINE_rec.price_list_line_id
);
l_PRICE_LIST_LINE_rec.last_update_date := SYSDATE;
l_PRICE_LIST_LINE_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_PRICE_LIST_LINE_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
IF l_PRICE_LIST_LINE_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
OE_Price_List_Line_Util.Update_Row (l_PRICE_LIST_LINE_rec);
OE_Price_List_Line_Util.Insert_Row (l_PRICE_LIST_LINE_rec);
SELECT
QPLST.ROUNDING_FACTOR
, QPLIN.ARITHMETIC_OPERATOR
, QPLIN.LIST_PRICE
, QPLIN.PERCENT_PRICE
INTO
l_rounding_factor
, l_prc_method_code_out
, l_list_price
, l_percent_price
FROM QP_LIST_HEADERS QPLST
, QP_LIST_LINES QPLIN
, QP_PRICING_ATTRIBUTES QPPRC
WHERE QPLST.LIST_HEADER_ID = QPLIN.LIST_HEADER_ID
AND QPLIN.LIST_LINE_ID = QPPRC.LIST_LINE_ID
AND QPLIN.LIST_LINE_TYPE_CODE = G_PRC_PRICE_LIST_LINE
AND DECODE(QPLIN.LIST_PRICE,NULL,G_PRC_METHOD_PERCENT,G_PRC_METHOD_AMOUNT) =
NVL( l_prc_method_code,DECODE(QPLIN.LIST_PRICE,NULL,G_PRC_METHOD_PERCENT,G_PRC_METHOD_AMOUNT) )
AND TRUNC(L_PRICING_DATE)
BETWEEN NVL( QPLIN.START_DATE_ACTIVE, TRUNC(L_PRICING_DATE) )
AND NVL( QPLIN.END_DATE_ACTIVE, TRUNC(L_PRICING_DATE) )
AND Decode(l_pricing_attribute1,Null,
QP_PRICE_LIST_PVT.Does_Pricing_Attribute_Exist ( 'ITEM', 'PRICING_ATTRIBUTE1',
p_inventory_item_id, p_unit_code,
--the following two attributes must
-- not be hardcoded...Kannan..10/01/99
'ALL',
'ALL',
'ALL',
Null,
qplin.list_line_id,
qplin.list_header_id),
QP_PRICE_LIST_PVT.Does_Pricing_Attribute_Exist (
'ITEM', 'PRICING_ATTRIBUTE1',
p_inventory_item_id, p_unit_code,
--the following two attributes must
-- not be hardcoded...Kannan..10/01/99
'PRICING_ATTRIBUTE_CONTEXT',
'PRICING_ATTRIBUTE1',
l_pricing_attribute1,
l_pricing_attribute1,
qplin.list_line_id,
qplin.list_header_id)) = 'Y'
AND Decode(l_pricing_attribute2,Null,'Y',QP_PRICE_LIST_PVT.Does_Pricing_Attribute_Exist ( 'ITEM', 'PRICING_ATTRIBUTE1',
p_inventory_item_id, p_unit_code,
--the following two attributes must
-- not be hardcoded...Kannan..10/01/99
'PRICING_ATTRIBUTE_CONTEXT',
'PRICING_ATTRIBUTE2',
l_pricing_attribute2,
l_pricing_attribute2,
qplin.list_line_id,
qplin.list_header_id)) = 'Y'
AND Decode(l_pricing_attribute3,Null,'Y',QP_PRICE_LIST_PVT.Does_Pricing_Attribute_Exist ( 'ITEM', 'PRICING_ATTRIBUTE1',
p_inventory_item_id, p_unit_code,
--the following two attributes must
-- not be hardcoded...Kannan..10/01/99
'PRICING_ATTRIBUTE_CONTEXT',
'PRICING_ATTRIBUTE3',
l_pricing_attribute3,
l_pricing_attribute3,
qplin.list_line_id,
qplin.list_header_id)) = 'Y'
AND Decode(l_pricing_attribute4,Null,'Y',QP_PRICE_LIST_PVT.Does_Pricing_Attribute_Exist ( 'ITEM', 'PRICING_ATTRIBUTE1',
p_inventory_item_id, p_unit_code,
--the following two attributes must
-- not be hardcoded...Kannan..10/01/99
'PRICING_ATTRIBUTE_CONTEXT',
'PRICING_ATTRIBUTE4',
l_pricing_attribute4,
l_pricing_attribute4,
qplin.list_line_id,
qplin.list_header_id)) = 'Y'
AND Decode(l_pricing_attribute5,Null,'Y',QP_PRICE_LIST_PVT.Does_Pricing_Attribute_Exist ( 'ITEM', 'PRICING_ATTRIBUTE1',
p_inventory_item_id, p_unit_code,
--the following two attributes must
-- not be hardcoded...Kannan..10/01/99
'PRICING_ATTRIBUTE_CONTEXT',
'PRICING_ATTRIBUTE5',
l_pricing_attribute5,
l_pricing_attribute5,
qplin.list_line_id,
qplin.list_header_id)) = 'Y'
AND Decode(l_pricing_attribute6,Null,'Y',QP_PRICE_LIST_PVT.Does_Pricing_Attribute_Exist ( 'ITEM', 'PRICING_ATTRIBUTE1',
p_inventory_item_id, p_unit_code,
--the following two attributes must
-- not be hardcoded...Kannan..10/01/99
'PRICING_ATTRIBUTE_CONTEXT',
'PRICING_ATTRIBUTE6',
l_pricing_attribute6,
l_pricing_attribute6,
qplin.list_line_id,
qplin.list_header_id)) = 'Y'
AND Decode(l_pricing_attribute7,Null,'Y',QP_PRICE_LIST_PVT.Does_Pricing_Attribute_Exist ( 'ITEM', 'PRICING_ATTRIBUTE1',
p_inventory_item_id, p_unit_code,
--the following two attributes must
-- not be hardcoded...Kannan..10/01/99
'PRICING_ATTRIBUTE_CONTEXT',
'PRICING_ATTRIBUTE7',
l_pricing_attribute7,
l_pricing_attribute7,
qplin.list_line_id,
qplin.list_header_id)) = 'Y'
AND Decode(l_pricing_attribute8,Null,'Y',QP_PRICE_LIST_PVT.Does_Pricing_Attribute_Exist ( 'ITEM', 'PRICING_ATTRIBUTE1',
p_inventory_item_id, p_unit_code,
--the following two attributes must
-- not be hardcoded...Kannan..10/01/99
'PRICING_ATTRIBUTE_CONTEXT',
'PRICING_ATTRIBUTE8',
l_pricing_attribute8,
l_pricing_attribute8,
qplin.list_line_id,
qplin.list_header_id)) = 'Y'
AND Decode(l_pricing_attribute9,Null,'Y',QP_PRICE_LIST_PVT.Does_Pricing_Attribute_Exist ( 'ITEM', 'PRICING_ATTRIBUTE1',
p_inventory_item_id, p_unit_code,
--the following two attributes must
-- not be hardcoded...Kannan..10/01/99
'PRICING_ATTRIBUTE_CONTEXT',
'PRICING_ATTRIBUTE9',
l_pricing_attribute9,
l_pricing_attribute9,
qplin.list_line_id,
qplin.list_header_id)) = 'Y'
AND Decode(l_pricing_attribute10,Null,'Y',QP_PRICE_LIST_PVT.Does_Pricing_Attribute_Exist ( 'ITEM', 'PRICING_ATTRIBUTE1',
p_inventory_item_id, p_unit_code,
--the following two attributes must
-- not be hardcoded...Kannan..10/01/99
'PRICING_ATTRIBUTE_CONTEXT',
'PRICING_ATTRIBUTE10',
l_pricing_attribute10,
l_pricing_attribute10,
qplin.list_line_id,
qplin.list_header_id)) = 'Y'
AND Decode(l_pricing_attribute11,Null,'Y',QP_PRICE_LIST_PVT.Does_Pricing_Attribute_Exist ( 'ITEM', 'PRICING_ATTRIBUTE1',
p_inventory_item_id, p_unit_code,
--the following two attributes must
-- not be hardcoded...Kannan..10/01/99
'PRICING_ATTRIBUTE_CONTEXT',
'PRICING_ATTRIBUTE11',
l_pricing_attribute11,
l_pricing_attribute11,
qplin.list_line_id,
qplin.list_header_id)) = 'Y'
AND Decode(l_pricing_attribute12,Null,'Y',QP_PRICE_LIST_PVT.Does_Pricing_Attribute_Exist ( 'ITEM', 'PRICING_ATTRIBUTE1',
p_inventory_item_id, p_unit_code,
--the following two attributes must
-- not be hardcoded...Kannan..10/01/99
'PRICING_ATTRIBUTE_CONTEXT',
'PRICING_ATTRIBUTE12',
l_pricing_attribute12,
l_pricing_attribute12,
qplin.list_line_id,
qplin.list_header_id)) = 'Y'
AND Decode(l_pricing_attribute13,Null,'Y',QP_PRICE_LIST_PVT.Does_Pricing_Attribute_Exist ( 'ITEM', 'PRICING_ATTRIBUTE1',
p_inventory_item_id, p_unit_code,
--the following two attributes must
-- not be hardcoded...Kannan..10/01/99
'PRICING_ATTRIBUTE_CONTEXT',
'PRICING_ATTRIBUTE13',
l_pricing_attribute13,
l_pricing_attribute13,
qplin.list_line_id,
qplin.list_header_id)) = 'Y'
AND Decode(l_pricing_attribute14,Null,'Y',QP_PRICE_LIST_PVT.Does_Pricing_Attribute_Exist ( 'ITEM', 'PRICING_ATTRIBUTE1',
p_inventory_item_id, p_unit_code,
--the following two attributes must
-- not be hardcoded...Kannan..10/01/99
'PRICING_ATTRIBUTE_CONTEXT',
'PRICING_ATTRIBUTE14',
l_pricing_attribute14,
l_pricing_attribute14,
qplin.list_line_id,
qplin.list_header_id)) = 'Y'
AND Decode(l_pricing_attribute15,Null,'Y',QP_PRICE_LIST_PVT.Does_Pricing_Attribute_Exist ( 'ITEM', 'PRICING_ATTRIBUTE1',
p_inventory_item_id, p_unit_code,
--the following two attributes must
-- not be hardcoded...Kannan..10/01/99
'PRICING_ATTRIBUTE_CONTEXT',
'PRICING_ATTRIBUTE15',
l_pricing_attribute15,
l_pricing_attribute15,
qplin.list_line_id,
qplin.list_header_id)) = 'Y'
AND QPLST.LIST_HEADER_ID = p_price_list_id
AND TRUNC(L_PRICING_DATE)
BETWEEN NVL( QPLST.START_DATE_ACTIVE, TRUNC(L_PRICING_DATE) )
AND NVL( QPLST.END_DATE_ACTIVE, TRUNC(L_PRICING_DATE) );
-- List percent is the selected list price
l_list_percent := l_percent_price ;
END; -- BEGIN select list price block.
SELECT DESCRIPTION
INTO l_desc
FROM MTL_SYSTEM_ITEMS
WHERE INVENTORY_ITEM_ID = p_item_id
AND ORGANIZATION_ID = l_org_id;
SELECT UNIT_OF_MEASURE
INTO l_name
FROM MTL_UNITS_OF_MEASURE
WHERE UOM_CODE = p_unit_code;
select list_header_id
into l_sec_price_list_id
from qp_qualifiers
where qualifier_context = l_context
and qualifier_attribute = l_attribute
and qualifier_attr_value = to_char(p_list_header_id)
and list_header_id <> p_list_header_id
and list_header_id in ( select list_header_id
from qp_list_headers_b
where list_type_code = 'PRL' )
and qualifier_rule_id is null
and rownum < 2;
select to_number(product_attr_value)
into l_inventory_item_id
from qp_pricing_attributes
where list_line_id = p_list_line_id
and product_attribute_context = l_context
and product_attribute = l_attribute
and rownum = 1;
select to_number(pricing_attr_value_from)
into l_customer_item_id
from qp_pricing_attributes
where list_line_id = p_list_line_id
and pricing_attribute_context = l_context
and pricing_attribute = l_attribute;
select pricing_attribute_context
into l_pricing_context
from qp_pricing_attributes
where list_line_id = p_list_line_id
and pricing_attribute in ('PRICING_ATTRIBUTE1',
'PRICING_ATTRIBUTE2',
'PRICING_ATTRIBUTE3',
'PRICING_ATTRIBUTE4',
'PRICING_ATTRIBUTE5',
'PRICING_ATTRIBUTE6',
'PRICING_ATTRIBUTE7',
'PRICING_ATTRIBUTE8',
'PRICING_ATTRIBUTE9',
'PRICING_ATTRIBUTE10',
'PRICING_ATTRIBUTE11',
'PRICING_ATTRIBUTE12',
'PRICING_ATTRIBUTE13',
'PRICING_ATTRIBUTE14',
'PRICING_ATTRIBUTE15' )
and rownum = 1;
select pricing_attr_value_from
into l_pricing_attribute1
from qp_pricing_attributes
where list_line_id = p_list_line_id
and pricing_attribute = p_pricing_attr;
SELECT 'x'
Into Dummy_Variable
from QP_PRICING_ATTRIBUTES QPPA --, QP_LIST_LINES QPLL
Where
PRODUCT_ATTRIBUTE_CONTEXT = P_Product_Attr_Context
And PRODUCT_ATTRIBUTE = P_Product_Attr
And PRODUCT_ATTR_VALUE = P_Product_Attr_Val
And PRODUCT_UOM_CODE = P_PRODUCT_UOM_CODE
And PRICING_ATTRIBUTE_CONTEXT = P_PRICING_ATTRIBUTE_CONTEXT
And PRICING_ATTRIBUTE = P_PRICING_ATTRIBUTE
And P_PRICING_ATTR_VALUE_FROM between
PRICING_ATTR_VALUE_FROM And Nvl(PRICING_ATTR_VALUE_TO,PRICING_ATTR_VALUE_FROM)
-- AND QPPA.LIST_LINE_ID = QPLL.LIST_LINE_ID
AND QPPA.LIST_LINE_ID = P_LIST_LINE_ID;
select to_number(pricing_attr_value_to) into
l_price_break_high
from qp_pricing_attributes
where list_line_id = p_list_line_id
and pricing_attribute_context = l_context
and pricing_attribute = l_attribute;
select to_number(pricing_attr_value_from) into
l_price_break_low
from qp_pricing_attributes
where list_line_id = p_list_line_id
and pricing_attribute_context = l_context
and pricing_attribute = l_attribute;
select product_uom_code
into l_uom_code
from qp_pricing_attributes
where list_line_id = p_list_line_id
and product_attribute_context = l_context
and product_attribute = l_attribute
and rownum = 1;