The following lines contain the word 'select', 'insert', 'update' or 'delete':
if l_PRICE_LIST_rec.operation in (QP_GLOBALS.G_OPR_CREATE, QP_GLOBALS.G_OPR_UPDATE)
--and l_control_rec.called_from_ui = 'N' --this is not true for price locking API call
and l_PRICE_LIST_rec.list_source_code IS NOT NULL
and l_PRICE_LIST_rec.global_flag = 'N'
and nvl(QP_SECURITY.security_on, 'N') = 'N' then
l_PRICE_LIST_rec.global_flag := 'Y';
ELSIF l_PRICE_LIST_rec.operation = QP_GLOBALS.G_OPR_UPDATE
OR l_PRICE_LIST_rec.operation = QP_GLOBALS.G_OPR_DELETE
THEN
l_PRICE_LIST_rec.db_flag := FND_API.G_TRUE;
SELECT pte_code, source_system_code
INTO l_saved_pte_code, l_saved_source_system_code
FROM qp_list_headers_b
WHERE list_header_id = l_PRICE_LIST_rec.list_header_id;
IF ( l_PRICE_LIST_rec.operation = QP_GLOBALS.G_OPR_UPDATE
or l_PRICE_LIST_rec.operation = QP_GLOBALS.G_OPR_CREATE
or l_PRICE_LIST_rec.operation = QP_GLOBALS.G_OPR_DELETE ) THEN
IF l_control_rec.default_attributes
OR l_control_rec.change_attributes
THEN
IF p_validation_level > FND_API.G_VALID_LEVEL_NONE THEN
QP_Validate_Price_List.Attributes
( x_return_status => l_return_status
, p_PRICE_LIST_rec => l_PRICE_LIST_rec
, p_old_PRICE_LIST_rec => l_old_PRICE_LIST_rec
);
IF l_PRICE_LIST_rec.operation = QP_GLOBALS.G_OPR_DELETE THEN
QP_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 = QP_GLOBALS.G_OPR_DELETE THEN
FND_MESSAGE.SET_NAME('QP', 'QP_CANNOT_DELETE_PRICE_LIST');
/*QP_Price_List_Util.Delete_Row
( p_list_header_id => l_PRICE_LIST_rec.list_header_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 = QP_GLOBALS.G_OPR_UPDATE THEN
QP_Price_List_Util.Update_Row (l_PRICE_LIST_rec);
QP_Price_List_Util.Insert_Row (l_PRICE_LIST_rec);
END IF; /* if operation is create, update or delete */
qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
(p_entity_code => QP_GLOBALS.G_ENTITY_PRICE_LIST,
p_entity_id => l_PRICE_LIST_rec.list_header_id,
x_return_status => l_return_status );
qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
(p_entity_code => QP_GLOBALS.G_ENTITY_PRICE_LIST,
p_entity_id => l_PRICE_LIST_rec.list_header_id,
x_return_status => l_return_status );
qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
(p_entity_code => QP_GLOBALS.G_ENTITY_PRICE_LIST,
p_entity_id => l_PRICE_LIST_rec.list_header_id,
x_return_status => l_return_status );
ELSIF l_PRICE_LIST_LINE_rec.operation = QP_GLOBALS.G_OPR_UPDATE
OR l_PRICE_LIST_LINE_rec.operation = QP_GLOBALS.G_OPR_DELETE
THEN
l_PRICE_LIST_LINE_rec.db_flag := FND_API.G_TRUE;
SELECT pte_code, source_system_code
INTO l_saved_pte_code, l_saved_source_system_code
FROM qp_list_headers_b
WHERE list_header_id = l_PRICE_LIST_LINE_rec.list_header_id;
IF ( l_PRICE_LIST_LINE_rec.operation = QP_GLOBALS.G_OPR_UPDATE
or l_PRICE_LIST_LINE_rec.operation = QP_GLOBALS.G_OPR_CREATE
or l_PRICE_LIST_LINE_rec.operation = QP_GLOBALS.G_OPR_DELETE ) THEN
IF l_control_rec.default_attributes
OR l_control_rec.change_attributes
THEN
IF p_validation_level > FND_API.G_VALID_LEVEL_NONE THEN
QP_Validate_Price_List_Line.Attributes
( x_return_status => l_return_status
, p_PRICE_LIST_LINE_rec => l_PRICE_LIST_LINE_rec
, p_old_PRICE_LIST_LINE_rec => l_old_PRICE_LIST_LINE_rec
);
SELECT pte_code, source_system_code
INTO l_saved_pte_code, l_saved_source_system_code
FROM qp_list_headers_b
WHERE list_header_id = l_PRICE_LIST_LINE_rec.list_header_id;
IF l_PRICE_LIST_LINE_rec.operation = QP_GLOBALS.G_OPR_DELETE THEN
QP_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 = QP_GLOBALS.G_OPR_DELETE THEN
QP_Price_List_Line_Util.Delete_Row
( p_list_line_id => l_PRICE_LIST_LINE_rec.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 = QP_GLOBALS.G_OPR_UPDATE THEN
QP_Price_List_Line_Util.Update_Row (l_PRICE_LIST_LINE_rec);
QP_Price_List_Line_Util.Insert_Row (l_PRICE_LIST_LINE_rec);
select QP_RLTD_MODIFIERS_S.nextval
into l_rltd_modifiers_s from dual;
QP_RLTD_MODIFIER_PVT.Insert_Row(
l_rltd_modifiers_s
, l_PRICE_LIST_LINE_rec.creation_date
, l_PRICE_LIST_LINE_rec.created_by
, l_price_list_line_rec.last_update_date
, l_price_list_line_rec.last_updated_by
, l_price_list_line_rec.last_update_login
, l_price_list_line_rec.rltd_modifier_group_no
, l_price_list_line_rec.from_rltd_modifier_id
, l_price_list_line_rec.list_line_id
, 'PRICE BREAK'
, null
, null
, null
, null
, null
, null
, null
, null
, null
, null
, null
, null
, null
, null
, null
, null
);
p_request_type =>QP_GLOBALS.G_UPDATE_LINE_QUAL_IND,
x_return_status => l_return_status);
END IF; /* if operation is create, update or delete */
qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
(p_entity_code => QP_GLOBALS.G_ENTITY_PRICE_LIST_LINE,
p_entity_id => l_PRICE_LIST_LINE_rec.list_line_id,
x_return_status => l_return_status );
qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
(p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
p_entity_id => l_PRICE_LIST_LINE_rec.list_line_id,
x_return_status => l_return_status );
qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
(p_entity_code => QP_GLOBALS.G_ENTITY_PRICE_LIST_LINE,
p_entity_id => l_PRICE_LIST_LINE_rec.list_line_id,
x_return_status => l_return_status );
qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
(p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
p_entity_id => l_PRICE_LIST_LINE_rec.list_line_id,
x_return_status => l_return_status );
qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
(p_entity_code => QP_GLOBALS.G_ENTITY_PRICE_LIST_LINE,
p_entity_id => l_PRICE_LIST_LINE_rec.list_line_id,
x_return_status => l_return_status );
qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
(p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
p_entity_id => l_PRICE_LIST_LINE_rec.list_line_id,
x_return_status => l_return_status );
ELSIF l_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_UPDATE
OR l_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_DELETE
THEN
l_QUALIFIERS_rec.db_flag := FND_API.G_TRUE;
SELECT h.pte_code, h.source_system_code, h.list_type_code
INTO l_saved_pte_code, l_saved_source_system_code,
l_list_type_code
FROM qp_list_headers_b h, qp_qualifiers q
WHERE h.list_header_id = q.list_header_id
AND q.qualifier_id = l_QUALIFIERS_rec.qualifier_id;
IF ( l_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_UPDATE
or l_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_CREATE
or l_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_DELETE ) THEN
IF l_control_rec.default_attributes
OR l_control_rec.change_attributes
THEN
IF p_validation_level > FND_API.G_VALID_LEVEL_NONE THEN
QP_Validate_Qualifiers.Attributes
( x_return_status => l_return_status
, p_QUALIFIERS_rec => l_QUALIFIERS_rec
, p_old_QUALIFIERS_rec => l_old_QUALIFIERS_rec
);
SELECT pte_code, source_system_code, list_type_code
INTO l_saved_pte_code, l_saved_source_system_code,
l_list_type_code
FROM qp_list_headers_b
WHERE list_header_id = l_QUALIFIERS_rec.list_header_id;
IF l_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_DELETE THEN
QP_Validate_Qualifiers.Entity_Delete
( x_return_status => l_return_status
, p_QUALIFIERS_rec => l_QUALIFIERS_rec
);
SELECT LIST_TYPE_CODE INTO l_list_type_code
FROM QP_LIST_HEADERS_B WHERE
LIST_HEADER_ID = l_QUALIFIERS_rec.LIST_HEADER_ID;
SELECT COUNT(*) INTO v_count FROM
QP_QUALIFIERS WHERE
LIST_HEADER_ID = l_QUALIFIERS_rec.list_header_id
AND QUALIFIER_CONTEXT = 'MODLIST'
AND QUALIFIER_ATTRIBUTE = 'QUALIFIER_ATTRIBUTE4';
if l_qualifiers_rec.operation <> QP_GLOBALS.G_OPR_DELETE
then
SELECT COUNT(*)
INTO v_count
FROM qp_secondary_price_lists_v
WHERE parent_price_list_id =
l_qualifiers_rec.qualifier_attr_value;
IF l_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_DELETE THEN
IF l_QUALIFIERS_rec.QUALIFIER_CONTEXT = 'MODLIST' AND
l_QUALIFIERS_rec.QUALIFIER_ATTRIBUTE =
'QUALIFIER_ATTRIBUTE3' THEN
--fix for bug 1501138 not allow deletion of coupon qualifiers
oe_debug_pub.add('1501138 not allow delete qual');
FND_MESSAGE.SET_NAME('QP', 'QP_CANNOT_DELETE_THIS_QUAL');
QP_Qualifiers_Util.Delete_Row
( p_qualifier_id => l_QUALIFIERS_rec.qualifier_id
);
l_QUALIFIERS_rec.last_update_date := SYSDATE;
l_QUALIFIERS_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_QUALIFIERS_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
IF l_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_UPDATE THEN
QP_Qualifiers_Util.Update_Row (l_QUALIFIERS_rec);
QP_Qualifiers_Util.Update_Coupon_Row (l_QUALIFIERS_rec); ---Added for bug 7315016
QP_Qualifiers_Util.Insert_Row (l_QUALIFIERS_rec);
END IF; /* if operation is create, update or delete */
qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
(p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
p_entity_id => l_QUALIFIERS_rec.list_header_id,
x_return_status => l_return_status );
qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
(p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
p_entity_id => l_QUALIFIERS_rec.list_line_id,
x_return_status => l_return_status );
qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
(p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
p_entity_id => l_QUALIFIERS_rec.qualifier_id,
x_return_status => l_return_status );
qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
(p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
p_entity_id => l_QUALIFIERS_rec.list_header_id,
x_return_status => l_return_status );
qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
(p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
p_entity_id => l_QUALIFIERS_rec.list_line_id,
x_return_status => l_return_status );
qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
(p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
p_entity_id => l_QUALIFIERS_rec.qualifier_id,
x_return_status => l_return_status );
qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
(p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
p_entity_id => l_QUALIFIERS_rec.list_header_id,
x_return_status => l_return_status );
qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
(p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
p_entity_id => l_QUALIFIERS_rec.list_line_id,
x_return_status => l_return_status );
qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
(p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
p_entity_id => l_QUALIFIERS_rec.qualifier_id,
x_return_status => l_return_status );
SELECT RLTD_MODIFIER_GRP_TYPE
INTO l_rltd_modifier_grp_type
FROM qp_rltd_modifiers rm
WHERE rm.TO_RLTD_MODIFIER_ID = l_PRICING_ATTR_rec.list_line_id;
ELSIF l_PRICING_ATTR_rec.operation = QP_GLOBALS.G_OPR_UPDATE
OR l_PRICING_ATTR_rec.operation = QP_GLOBALS.G_OPR_DELETE
THEN
oe_debug_pub.add('operation is update');
SELECT h.pte_code, h.source_system_code
INTO l_saved_pte_code, l_saved_source_system_code
FROM qp_list_headers_b h, qp_list_lines l
WHERE h.list_header_id = l.list_header_id
AND l.list_line_id = l_PRICING_ATTR_rec.list_line_id;
IF ( l_PRICING_ATTR_rec.operation = QP_GLOBALS.G_OPR_UPDATE
or l_PRICING_ATTR_rec.operation = QP_GLOBALS.G_OPR_CREATE
or l_PRICING_ATTR_rec.operation = QP_GLOBALS.G_OPR_DELETE ) THEN
IF l_control_rec.default_attributes
OR l_control_rec.change_attributes
THEN
IF p_validation_level > FND_API.G_VALID_LEVEL_NONE THEN
oe_debug_pub.add('validate the attributes');
SELECT h.pte_code, h.source_system_code
INTO l_saved_pte_code, l_saved_source_system_code
FROM qp_list_headers_b h, qp_list_lines l
WHERE h.list_header_id = l.list_header_id
AND l.list_line_id = l_PRICING_ATTR_rec.list_line_id;
IF l_PRICING_ATTR_rec.operation = QP_GLOBALS.G_OPR_DELETE THEN
QP_Validate_pll_pricing_attr.Entity_Delete
( x_return_status => l_return_status
, p_PRICING_ATTR_rec => l_PRICING_ATTR_rec
);
IF l_PRICING_ATTR_rec.operation = QP_GLOBALS.G_OPR_DELETE THEN
Qp_pll_pricing_attr_Util.Delete_Row
( p_pricing_attribute_id => l_PRICING_ATTR_rec.pricing_attribute_id
);
l_PRICING_ATTR_rec.last_update_date := SYSDATE;
l_PRICING_ATTR_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_PRICING_ATTR_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
IF l_PRICING_ATTR_rec.operation = QP_GLOBALS.G_OPR_UPDATE THEN
oe_debug_Pub.add('before update row');
Qp_pll_pricing_attr_Util.Update_Row (l_PRICING_ATTR_rec);
oe_debug_pub.add('before insert row');
Qp_pll_pricing_attr_Util.Insert_Row (l_PRICING_ATTR_rec);
END IF; /* if operation is create, update or delete */
qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
(p_entity_code => QP_GLOBALS.G_ENTITY_PRICE_LIST_LINE,
p_entity_id => l_PRICING_ATTR_rec.list_line_id,
x_return_status => l_return_status );
qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
(p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
p_entity_id => l_PRICING_ATTR_rec.list_line_id,
x_return_status => l_return_status );
qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
(p_entity_code => QP_GLOBALS.G_ENTITY_PRICE_LIST_LINE,
p_entity_id => l_PRICING_ATTR_rec.list_line_id,
x_return_status => l_return_status );
qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
(p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
p_entity_id => l_PRICING_ATTR_rec.list_line_id,
x_return_status => l_return_status );
qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
(p_entity_code => QP_GLOBALS.G_ENTITY_PRICE_LIST_LINE,
p_entity_id => l_PRICING_ATTR_rec.list_line_id,
x_return_status => l_return_status );
qp_delayed_requests_pvt.delete_reqs_for_deleted_entity
(p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
p_entity_id => l_PRICING_ATTR_rec.list_line_id,
x_return_status => l_return_status );
,p_delete => FND_API.G_TRUE
,x_return_status => l_return_status
);
,p_delete => FND_API.G_TRUE
,x_return_status => l_return_status
);
select 'Y' into l_qual_exists
from qp_qualifiers
where list_header_id = x_PRICE_LIST_rec.list_header_id
and qualifier_context = 'ORDER'
and qualifier_attribute = 'QUALIFIER_ATTRIBUTE5'
and qualifier_attr_value = x_PRICE_LIST_rec.orig_system_header_ref;