The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT offer_code
FROM ozf_offers
WHERE qp_list_header_id = l_id;
SELECT custom_setup_id
FROM ams_custom_setups_vl
WHERE object_type = 'OFFR'
AND activity_type_code = p_act_offer_rec.offer_type;
SELECT ozf_act_offers_s.NEXTVAL
FROM DUAL;
SELECT COUNT(*)
FROM ozf_act_offers
WHERE activity_offer_id = act_offer_id;
INSERT INTO OZF_ACT_OFFERS
(
activity_offer_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
object_version_number,
act_offer_used_by_id,
arc_act_offer_used_by,
primary_offer_flag,
-- offer_type,
offer_code,
active_period_set,
active_period,
-- start_date,
-- end_date,
-- order_date_from,
-- order_date_to,
-- ship_date_from,
-- ship_date_to,
-- perf_date_from,
-- perf_date_to,
-- status_code,
-- status_date,
-- offer_amount,
-- lumpsum_payment_type,
qp_list_header_id
)
VALUES
(
l_act_offer_rec.activity_offer_id,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.conc_login_id,
1,
l_act_offer_rec.act_offer_used_by_id,
l_act_offer_rec.arc_act_offer_used_by,
l_act_offer_rec.primary_offer_flag,
-- l_act_offer_rec.offer_type,
l_offer_code,
l_act_offer_rec.active_period_set,
l_act_offer_rec.active_period,
-- l_act_offer_rec.start_date,
-- l_act_offer_rec.end_date,
-- l_act_offer_rec.order_date_from,
-- l_act_offer_rec.order_date_to,
-- l_act_offer_rec.ship_date_from,
-- l_act_offer_rec.ship_date_to,
-- l_act_offer_rec.perf_date_from,
-- l_act_offer_rec.perf_date_to,
-- l_act_offer_rec.status_code,
-- l_act_offer_rec.status_date,
-- l_act_offer_rec.offer_amount,
-- l_act_offer_rec.lumpsum_payment_type,
l_act_offer_rec.qp_list_header_id
);
PROCEDURE Update_Act_Offer
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.g_false,
p_commit IN VARCHAR2 := FND_API.g_false,
p_validation_level IN NUMBER := FND_API.g_valid_level_full,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_act_offer_rec IN act_offer_rec_type
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'update_act_offer';
SAVEPOINT update_act_offer;
p_validation_mode => JTF_PLSQL_API.g_update,
x_return_status => l_return_status,
p_act_offer_rec => l_act_offer_rec
);
UPDATE ozf_act_offers SET
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
object_version_number = l_act_offer_rec.object_version_number + 1,
last_update_login = FND_GLOBAL.conc_login_id,
act_offer_used_by_id = l_act_offer_rec.act_offer_used_by_id,
arc_act_offer_used_by = l_act_offer_rec.arc_act_offer_used_by,
primary_offer_flag = l_act_offer_rec.primary_offer_flag,
-- offer_type = l_act_offer_rec.offer_type,
-- offer_code = l_act_offer_rec.offer_code,
active_period_set = l_act_offer_rec.active_period_set,
active_period = l_act_offer_rec.active_period,
-- start_date = l_act_offer_rec.start_date,
-- end_date = l_act_offer_rec.end_date,
-- order_date_from = l_act_offer_rec.order_date_from,
-- order_date_to = l_act_offer_rec.order_date_to,
-- ship_date_from = l_act_offer_rec.ship_date_from,
-- ship_date_to = l_act_offer_rec.ship_date_to,
-- perf_date_from = l_act_offer_rec.perf_date_from,
-- perf_date_to = l_act_offer_rec.perf_date_to,
-- status_code = l_act_offer_rec.status_code,
-- status_date = l_act_offer_rec.status_date,
-- offer_amount = l_act_offer_rec.offer_amount,
-- lumpsum_payment_type = l_act_offer_rec.lumpsum_payment_type,
qp_list_header_id = l_act_offer_rec.qp_list_header_id
WHERE activity_offer_id = l_act_offer_rec.activity_offer_id
AND object_version_number = l_act_offer_rec.object_version_number;
ROLLBACK TO update_act_offer;
ROLLBACK TO update_act_offer;
ROLLBACK TO update_act_offer;
END Update_Act_Offer;
PROCEDURE Delete_Act_Offer
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.g_false,
p_commit IN VARCHAR2 := FND_API.g_false,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_act_offer_id IN NUMBER,
p_object_version IN NUMBER
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'delete_act_offer';
SELECT act_offer_used_by_id, arc_act_offer_used_by
FROM ozf_act_offers
WHERE activity_offer_id = p_act_offer_id;
SELECT 1
FROM ozf_act_offers
WHERE act_offer_used_by_id = l_used_by_id
AND arc_act_offer_used_by = l_used_by;
SAVEPOINT delete_act_offer;
DELETE FROM OZF_ACT_OFFERS
WHERE activity_offer_id = p_act_offer_id
AND object_version_number = p_object_version;
ROLLBACK TO delete_act_offer;
ROLLBACK TO delete_act_offer;
ROLLBACK TO delete_act_offer;
END Delete_Act_Offer;
SELECT activity_offer_id
FROM OZF_ACT_OFFERS
WHERE activity_offer_id = p_act_offer_id
AND object_version_number = p_object_version
FOR UPDATE OF activity_offer_id NOWAIT;
AND p_validation_mode = JTF_PLSQL_API.g_update
THEN
IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
FND_MESSAGE.set_name('OZF', 'OZF_ACT_OFFER_NO_ACT_OFFER_ID');
AND p_validation_mode = JTF_PLSQL_API.g_update
THEN
IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
FND_MESSAGE.set_name('OZF', 'OZF_API_NO_OBJ_VER_NUM');
ELSIF p_validation_mode = JTF_PLSQL_API.g_update
AND p_act_offer_rec.activity_offer_id IS NOT NULL
THEN
l_uk_flag := OZF_Utility_PVT.check_uniqueness
(
'OZF_ACT_OFFERS',
' activity_offer_id <> '|| p_act_offer_rec.activity_offer_id ||
' AND qp_list_header_id = ' || p_act_offer_rec.qp_list_header_id ||
' AND act_offer_used_by_id = ' || p_act_offer_rec.act_offer_used_by_id ||
' AND arc_act_offer_used_by = ' ||p_act_offer_rec.arc_act_offer_used_by
);
SELECT count(*) FROM AMS_CAMPAIGNS_VL
WHERE campaign_id = used_by_id
AND (actual_exec_end_date IS NULL
-- changed by rssharma for bug fixing on 01/26/2001
OR actual_exec_end_date >= trunc(SYSDATE) );
SELECT COUNT(*) FROM OZF_ACT_OFFERS
WHERE act_offer_used_by_id = used_by_id
AND arc_act_offer_used_by = used_by
AND primary_offer_flag = 'Y';
SELECT COUNT(*) FROM OZF_ACT_OFFERS
WHERE act_offer_used_by_id = used_by_id
AND arc_act_offer_used_by = used_by
AND activity_offer_id <> act_offer_id
AND primary_offer_flag = 'Y';
SELECT *
FROM ozf_act_offers
WHERE activity_offer_id = p_act_offer_rec.activity_offer_id;
x_act_offer_rec.last_update_date := FND_API.g_miss_date;
x_act_offer_rec.last_updated_by := FND_API.g_miss_num;
x_act_offer_rec.last_update_login := FND_API.g_miss_num;
PROCEDURE Update_Offer
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.g_false,
p_commit IN VARCHAR2 := FND_API.g_false,
p_validation_level IN NUMBER := FND_API.g_valid_level_full,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_act_offer_rec IN act_offer_rec_type,
p_list_header_id IN NUMBER := FND_API.g_miss_num,
p_offer_name IN VARCHAR2 := FND_API.g_miss_char,
p_currency_code IN VARCHAR2 := FND_API.g_miss_char,
p_start_date IN DATE := FND_API.g_miss_date,
p_end_date IN DATE := FND_API.g_miss_date,
p_active_flag IN VARCHAR2 := FND_API.g_miss_char,
p_automatic_flag IN VARCHAR2 := FND_API.g_miss_char,
x_message_type OUT NOCOPY VARCHAR2 -- OE / FND
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Offer';
SAVEPOINT Update_Offer;
p_mode => 'UPDATE',
x_list_header_id => l_list_header_id
);
OZF_Utility_Pvt.Debug_Message('Update Activity Offer');
Update_Act_Offer
(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => l_return_status ,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_act_offer_rec => p_act_offer_rec
) ;
ROLLBACK TO Update_Offer;
ROLLBACK TO Update_Offer;
ROLLBACK TO Update_Offer;
END Update_Offer;
PROCEDURE Delete_Offer
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.g_false,
p_commit IN VARCHAR2 := FND_API.g_false,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_act_offer_id IN NUMBER,
p_object_version IN NUMBER,
p_list_header_id IN NUMBER := FND_API.g_miss_num,
p_offer_name IN VARCHAR2 := FND_API.g_miss_char,
p_currency_code IN VARCHAR2 := FND_API.g_miss_char,
p_start_date IN DATE := FND_API.g_miss_date,
p_end_date IN DATE := FND_API.g_miss_date,
p_active_flag IN VARCHAR2 := FND_API.g_miss_char,
p_automatic_flag IN VARCHAR2 := FND_API.g_miss_char,
x_message_type OUT NOCOPY VARCHAR2 -- OE / FND
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Offer';
SAVEPOINT Delete_Offer;
p_mode => 'DELETE',
x_list_header_id => l_list_header_id
);
OZF_Utility_Pvt.Debug_Message('Delete Activity Offer');
Delete_Act_Offer
(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
x_return_status => l_return_status ,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_act_offer_id => p_act_offer_id,
p_object_version => p_object_version
) ;
ROLLBACK TO Delete_Offer;
ROLLBACK TO Delete_Offer;
ROLLBACK TO Delete_Offer;
END Delete_Offer;