The following lines contain the word 'select', 'insert', 'update' or 'delete':
IF p_PRICING_ATTR_rec.operation = QP_GLOBALS.G_OPR_UPDATE
THEN
IF nvl(p_old_PRICING_ATTR_rec.PRODUCT_ATTRIBUTE_CONTEXT,'X') <>
nvl(p_PRICING_ATTR_rec.PRODUCT_ATTRIBUTE_CONTEXT,'X')
THEN
l_return_status := FND_API.G_RET_STS_ERROR;
FND_MESSAGE.SET_NAME('QP','QP_CANNOT_UPDATE_ATTRIBUTE');
FND_MESSAGE.SET_NAME('QP','QP_CANNOT_UPDATE_ATTRIBUTE');
FND_MESSAGE.SET_NAME('QP','QP_CANNOT_UPDATE_ATTRIBUTE');
SELECT pte_code, source_system_code
INTO l_pte_code, l_ss_code
FROM qp_list_headers_b
WHERE list_header_id = p_PRICING_ATTR_rec.list_header_id;
SELECT primary_uom_flag
INTO l_primary_uom_flag
FROM qp_list_lines
WHERE list_line_id = p_PRICING_ATTR_rec.list_line_id;
select start_date_active, end_date_active , revision, list_header_id
into l_start_date_active, l_end_date_active, l_revision, l_list_header_id
from qp_list_lines
where list_line_id = p_PRICING_ATTR_rec.list_line_id;
select b.list_line_id col2
from qp_pricing_attributes b, qp_list_lines c
where b.list_line_id <> p_PRICING_ATTR_rec.list_line_id
and b.list_line_id=c.list_line_id
and c.primary_uom_flag='Y'
and b.product_attribute_context = p_PRICING_ATTR_rec.product_attribute_context
and b.product_attribute = p_PRICING_ATTR_rec.product_attribute
and b.product_attr_value = p_PRICING_ATTR_rec.product_attr_value
and b.list_header_id = p_PRICING_ATTR_rec.list_header_id
AND b.product_uom_code <> p_PRICING_ATTR_rec.product_uom_code -- for bug 7135111
)
Loop
/*----------------------------------------------*/
l_min_date := to_date('01/01/1900', 'MM/DD/YYYY');
SELECT start_date_active, end_date_active
into l_sdate, l_edate
from qp_list_lines
where list_line_id = c1.col2;
SELECT 'VALID' INTO l_dummy
FROM mtl_system_items_b
where inventory_item_id = p_PRICING_ATTR_rec.product_attr_value
AND customer_order_flag = 'Y'
and organization_id = fnd_profile.value('QP_ORGANIZATION_ID');
select 'VALID' INTO l_dummy
from mtl_item_uoms_view
where uom_code = p_PRICING_ATTR_rec.product_uom_code
and organization_id = fnd_profile.value('QP_ORGANIZATION_ID')
and inventory_item_id = p_PRICING_ATTR_rec.product_attr_value
and rownum = 1;
-- Bug 6891094 :UOM validity check to be done only for new list lines and lines in which the UOM is updated.
elsif (p_PRICING_ATTR_rec.product_attribute = 'PRICING_ATTRIBUTE2') THEN
IF p_PRICING_ATTR_rec.product_uom_code IS NOT NULL AND
( p_PRICING_ATTR_rec.product_uom_code <> p_old_PRICING_ATTR_rec.product_uom_code
OR
p_old_PRICING_ATTR_rec.product_uom_code IS NULL ) THEN
IF NOT QP_VALIDATE.Product_Uom(p_pricing_attr_rec.product_uom_code,
to_number(p_PRICING_ATTR_rec.product_attr_value),
p_PRICING_ATTR_rec.list_header_id) THEN
IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR) THEN
FND_MESSAGE.SET_NAME('QP','QP_INVALID_PROD_UOM');
SELECT 'VALID' INTO l_dummy
FROM MTL_UNITS_OF_MEASURE_VL MTLUOM2
WHERE
EXISTS
(
SELECT /*+no_unnest*--/ 1
FROM MTL_SYSTEM_ITEMS_B MTLITM1,
MTL_UOM_CONVERSIONS MTLUCV
WHERE MTLUOM2.UOM_CODE = MTLUCV.UOM_CODE
AND MTLUOM2.UOM_CODE = p_PRICING_ATTR_rec.product_uom_code
AND MTLITM1.organization_id = fnd_profile.value('QP_ORGANIZATION_ID')
AND MTLITM1.inventory_item_id in
(
SELECT inventory_item_id FROM mtl_item_categories
WHERE category_id = to_number(p_PRICING_ATTR_rec.product_attr_value)
AND organization_id = fnd_profile.value('QP_ORGANIZATION_ID')
)
AND NVL( MTLUCV.DISABLE_DATE, TRUNC(SYSDATE)+1 ) > TRUNC(SYSDATE)
AND
(
(
MTLITM1.ALLOWED_UNITS_LOOKUP_CODE IN (1, 3)
AND MTLUCV.INVENTORY_ITEM_ID = MTLITM1.INVENTORY_ITEM_ID
OR
(
MTLUCV.INVENTORY_ITEM_ID = 0
AND MTLUOM2.BASE_UOM_FLAG = 'Y'
AND MTLUOM2.UOM_CLASS = MTLUCV.UOM_CLASS
AND MTLUCV.UOM_CLASS IN
(
SELECT MTLPRI1.UOM_CLASS
FROM MTL_UNITS_OF_MEASURE MTLPRI1
WHERE MTLPRI1.UOM_CODE = MTLITM1.PRIMARY_UOM_CODE
)
)
OR
(
MTLUCV.INVENTORY_ITEM_ID = 0
AND MTLUCV.UOM_CODE IN
(
SELECT MTLUCC1.TO_UOM_CODE
FROM MTL_UOM_CLASS_CONVERSIONS MTLUCC1
WHERE MTLUCC1.INVENTORY_ITEM_ID = MTLITM1.INVENTORY_ITEM_ID
AND NVL(MTLUCC1.DISABLE_DATE, TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
)
)
)
OR
(
MTLITM1.ALLOWED_UNITS_LOOKUP_CODE IN (2, 3)
AND MTLUCV.INVENTORY_ITEM_ID = 0
AND
(
MTLUCV.UOM_CLASS IN
(
SELECT MTLUCC.TO_UOM_CLASS
FROM MTL_UOM_CLASS_CONVERSIONS MTLUCC
WHERE MTLUCC.INVENTORY_ITEM_ID = MTLITM1.INVENTORY_ITEM_ID
AND NVL(MTLUCC.DISABLE_DATE, TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
)
OR MTLUCV.UOM_CLASS =
(
SELECT MTLPRI.UOM_CLASS
FROM MTL_UNITS_OF_MEASURE MTLPRI
WHERE MTLPRI.UOM_CODE = MTLITM1.PRIMARY_UOM_CODE
)
)
)
)
)
and rownum = 1;
select 'VALID' INTO l_dummy
from MTL_UNITS_OF_MEASURE_VL
where uom_code = p_PRICING_ATTR_rec.product_uom_code
and rownum = 1;
SELECT count(*)
INTO l_count
FROM qp_list_lines l, qp_pricing_attributes a
WHERE l.list_line_id = a.list_line_id
AND a.list_header_id = p_PRICING_ATTR_rec.list_header_id
AND a.product_attribute_context =
p_PRICING_ATTR_rec.product_attribute_context
AND a.product_attribute = p_PRICING_ATTR_rec.product_attribute
AND a.product_attr_value = p_PRICING_ATTR_rec.product_attr_value
AND a.product_uom_code <> p_PRICING_ATTR_rec.product_uom_code
AND l.primary_uom_flag = 'Y';
select start_date_active, end_date_active , revision, list_header_id
into l_start_date_active, l_end_date_active, l_revision, l_list_header_id
from qp_list_lines
where list_line_id = p_PRICING_ATTR_rec.list_line_id;
SELECT lookup_code
INTO l_comparison_operator_code
FROM QP_LOOKUPS
WHERE LOOKUP_TYPE = 'COMPARISON_OPERATOR'
AND LOOKUP_CODE = UPPER(p_Pricing_Attr_rec.comparison_operator_code);
select from_rltd_modifier_id
into l_from_rltd_modifier_id
from qp_rltd_modifiers
where to_rltd_modifier_id = p_Pricing_Attr_rec.list_line_id;
select continuous_price_break_flag
into l_continuous_price_break_flag
from qp_list_lines
where list_line_id = l_from_rltd_modifier_id;
IF p_PRICING_ATTR_rec.last_updated_by IS NOT NULL AND
( p_PRICING_ATTR_rec.last_updated_by <>
p_old_PRICING_ATTR_rec.last_updated_by OR
p_old_PRICING_ATTR_rec.last_updated_by IS NULL )
THEN
IF NOT QP_Validate.Last_Updated_By(p_PRICING_ATTR_rec.last_updated_by) THEN
x_return_status := FND_API.G_RET_STS_ERROR;
IF p_PRICING_ATTR_rec.last_update_date IS NOT NULL AND
( p_PRICING_ATTR_rec.last_update_date <>
p_old_PRICING_ATTR_rec.last_update_date OR
p_old_PRICING_ATTR_rec.last_update_date IS NULL )
THEN
IF NOT QP_Validate.Last_Update_Date(p_PRICING_ATTR_rec.last_update_date) THEN
x_return_status := FND_API.G_RET_STS_ERROR;
IF p_PRICING_ATTR_rec.last_update_login IS NOT NULL AND
( p_PRICING_ATTR_rec.last_update_login <>
p_old_PRICING_ATTR_rec.last_update_login OR
p_old_PRICING_ATTR_rec.last_update_login IS NULL )
THEN
IF NOT QP_Validate.Last_Update_Login(p_PRICING_ATTR_rec.last_update_login) THEN
x_return_status := FND_API.G_RET_STS_ERROR;
IF p_PRICING_ATTR_rec.program_update_date IS NOT NULL AND
( p_PRICING_ATTR_rec.program_update_date <>
p_old_PRICING_ATTR_rec.program_update_date OR
p_old_PRICING_ATTR_rec.program_update_date IS NULL )
THEN
IF NOT QP_Validate.Program_Update_Date(p_PRICING_ATTR_rec.program_update_date) THEN
x_return_status := FND_API.G_RET_STS_ERROR;
PROCEDURE Entity_Delete
( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
, p_PRICING_ATTR_rec IN QP_Price_List_PUB.Pricing_Attr_Rec_Type
)
IS
l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
, 'Entity_Delete'
);
END Entity_Delete;
select a.list_line_id col1, b.list_line_id col2
from qp_pricing_attributes a, qp_pricing_attributes b
where a.list_line_id = l_List_Line_ID
and b.list_line_id <> l_List_Line_ID
and not exists (select Null
from qp_rltd_modifiers qrm
Where qrm.to_rltd_modifier_id = b.list_line_id
and qrm.rltd_modifier_grp_type = 'PRICE BREAK')
and b.product_attribute_context = a.product_attribute_context
and b.product_attribute = a.product_attribute
and b.product_attr_value = a.product_attr_value
and b.product_uom_code = a.product_uom_code --2943344
and nvl( b.pricing_attribute_context,' ') = nvl(a.pricing_attribute_context,' ')
and nvl(b.pricing_attribute,' ') = nvl(a.pricing_attribute,' ')
and nvl(b.pricing_attr_value_from,0) = nvl(a.pricing_attr_value_from,0)
and nvl(b.pricing_attr_value_to,0) = nvl(a.pricing_attr_value_to,0)
and nvl(b.comparison_operator_code,' ') = nvl(a.comparison_operator_code,' ') --Added for 2128739; julin: added nvl
and count(b.list_line_id) = ( select count(*) from qp_pricing_attributes where list_line_id = b.list_line_id); --2326820
select count(*)
Into l_attr_lines_count
from qp_pricing_attributes
where list_line_id = p_List_Line_ID;
SELECT revision, start_date_active, end_date_active
into l_count, l_sdate, l_edate
from qp_list_lines
where list_line_id = rec.col2;
select a.list_line_id, b.list_line_id
into l_dummy, l_dummy1
from qp_pricing_attributes a, qp_pricing_attributes b, qp_list_lines c
where a.list_line_id = p_List_Line_ID
and b.list_line_id <> p_List_Line_ID
and b.product_attribute_context = a.product_attribute_context
and b.product_attribute = a.product_attribute
and b.product_attr_value = a.product_attr_value
-- and nvl(b.product_attribute_context,' ') = nvl(a.product_attribute_context,' ') ** bug 2813068 **
-- and nvl(b.product_attribute,' ') = nvl(a.product_attribute,' ') ** bug 2813068 **
-- and nvl(b.product_attr_value,' ') = nvl(a.product_attr_value,' ') ** bug 2813068 **
and nvl(b.product_uom_code,' ') = nvl(a.product_uom_code,' ')
and nvl(b.pricing_attribute_context,' ') = nvl(a.pricing_attribute_context,' ')
and nvl(b.pricing_attribute,' ') = nvl(a.pricing_attribute,' ' )
and nvl(b.pricing_attr_value_from,' ') = nvl(a.pricing_attr_value_from,' ')
and nvl(b.pricing_attr_value_to,' ') = nvl(a.pricing_attr_value_to,' ')
and b.comparison_operator_code = a.comparison_operator_code --Added for 2128739
and a.list_line_id = c.list_line_id
and nvl(c.revision,' ') = nvl(p_Revision,' ')
group by a.list_line_id, b.list_line_id
having count(b.list_line_id) = ( select count(*)
from qp_pricing_attributes
where list_line_id = b.list_line_id) ;
select a.list_line_id, b.list_line_id
into l_dummy, l_dummy1
from qp_pricing_attributes a, qp_pricing_attributes b, qp_list_lines c
where a.list_line_id = p_List_Line_ID
and b.list_line_id <> p_List_Line_ID
and b.product_attribute_context = a.product_attribute_context
and b.product_attribute = a.product_attribute
and b.product_attr_value = a.product_attr_value
-- and nvl(b.product_attribute_context,' ') = nvl(a.product_attribute_context,' ') ** bug 2813068 **
-- and nvl(b.product_attribute,' ') = nvl(a.product_attribute,' ') ** bug 2813068 **
-- and nvl(b.product_attr_value,' ') = nvl(a.product_attr_value,' ') ** bug 2813068 **
and nvl(b.product_uom_code,' ') = nvl(a.product_uom_code,' ')
and nvl(b.pricing_attribute_context,' ') = nvl(a.pricing_attribute_context,' ')
and nvl(b.pricing_attribute,' ') = nvl(a.pricing_attribute,' ' )
and nvl(b.pricing_attr_value_from,' ') = nvl(a.pricing_attr_value_from,' ')
and nvl(b.pricing_attr_value_to,' ') = nvl(a.pricing_attr_value_to,' ')
and b.comparison_operator_code = a.comparison_operator_code --Added for 2128739
and a.list_line_id = c.list_line_id
and ( nvl(trunc(start_date_active),sysdate) BETWEEN nvl(to_date(to_char(p_Start_Date_Active,'DD/MM/YYYY'),'DD/MM/YYYY'), sysdate)
and nvl(to_date(to_char(p_End_Date_Active,'DD/MM/YYYY'),'DD/MM/YYYY'), sysdate) OR
nvl(trunc(end_date_active),sysdate) BETWEEN nvl(to_date(to_char(p_Start_Date_Active,'DD/MM/YYYY'),'DD/MM/YYYY'),sysdate )
and nvl(to_date(to_char(p_End_Date_Active,'DD/MM/YYYY'),'DD/MM/YYYY'), sysdate ) )
group by a.list_line_id, b.list_line_id
having count(b.list_line_id) = ( select count(*)
from qp_pricing_attributes
where list_line_id = b.list_line_id) ;