[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ams_campaign_schedules_b_s.NEXTVAL
FROM dual;
SELECT 1 FROM dual
WHERE EXISTS (SELECT 1 FROM AMS_CAMPAIGN_SCHEDULES_B
WHERE SCHEDULE_ID = l_id);
SELECT source_code
FROM ams_event_offers_all_b
WHERE event_offer_id = P_schedule_rec.related_event_id;
AMS_CAMPAIGN_SCHEDULES_B_PKG.Insert_Row(
px_schedule_id => l_schedule_id,
-- modified by soagrawa on 12-jun-2002 for ATT bug# 2376329
p_last_update_date => SYSDATE,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_creation_date => SYSDATE,
p_created_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID,
/*
p_last_update_date => SYSDATE,
p_last_updated_by => G_USER_ID,
p_creation_date => SYSDATE,
p_created_by => G_USER_ID,
p_last_update_login => G_LOGIN_ID,
*/
px_object_version_number => l_object_version_number,
p_campaign_id => p_schedule_rec.campaign_id,
p_user_status_id => p_schedule_rec.user_status_id,
p_status_code => l_schedule_rec.status_code,
p_status_date => NVL(p_schedule_rec.status_date,SYSDATE),
p_source_code => l_schedule_rec.source_code,
p_use_parent_code_flag => p_schedule_rec.use_parent_code_flag,
p_start_date_time => p_schedule_rec.start_date_time,
p_end_date_time => p_schedule_rec.end_date_time,
p_timezone_id => p_schedule_rec.timezone_id,
p_activity_type_code => p_schedule_rec.activity_type_code,
p_activity_id => p_schedule_rec.activity_id,
p_arc_marketing_medium_from => p_schedule_rec.arc_marketing_medium_from,
p_marketing_medium_id => p_schedule_rec.marketing_medium_id,
p_custom_setup_id => p_schedule_rec.custom_setup_id,
p_triggerable_flag => p_schedule_rec.triggerable_flag,
p_trigger_id => p_schedule_rec.trigger_id,
p_notify_user_id => p_schedule_rec.notify_user_id,
p_approver_user_id => p_schedule_rec.approver_user_id,
p_owner_user_id => p_schedule_rec.owner_user_id,
p_active_flag => p_schedule_rec.active_flag,
-- soagrawa removed on 03-nov-2003 for 11.5.10
p_cover_letter_id => null,
p_reply_to_mail => p_schedule_rec.reply_to_mail,
p_mail_sender_name => p_schedule_rec.mail_sender_name,
p_mail_subject => p_schedule_rec.mail_subject,
p_from_fax_no => p_schedule_rec.from_fax_no,
p_accounts_closed_flag => NVL(p_schedule_rec.accounts_closed_flag,'N'),
px_org_id => l_org_id,
p_objective_code => p_schedule_rec.objective_code,
p_country_id => p_schedule_rec.country_id,
p_campaign_calendar => l_schedule_rec.campaign_calendar,
p_start_period_name => p_schedule_rec.start_period_name,
p_end_period_name => p_schedule_rec.end_period_name,
p_priority => p_schedule_rec.priority,
p_workflow_item_key => p_schedule_rec.workflow_item_key,
p_transaction_currency_code => p_schedule_rec.transaction_currency_code,
p_functional_currency_code => l_schedule_rec.functional_currency_code,
p_budget_amount_tc => p_schedule_rec.budget_amount_tc,
p_budget_amount_fc => l_schedule_rec.budget_amount_fc,
p_language_code => p_schedule_rec.language_code,
p_task_id => p_schedule_rec.task_id,
p_related_event_from => l_schedule_rec.related_event_from,
p_related_event_id => l_schedule_rec.related_event_id,
p_attribute_category => p_schedule_rec.attribute_category,
p_attribute1 => p_schedule_rec.attribute1,
p_attribute2 => p_schedule_rec.attribute2,
p_attribute3 => p_schedule_rec.attribute3,
p_attribute4 => p_schedule_rec.attribute4,
p_attribute5 => p_schedule_rec.attribute5,
p_attribute6 => p_schedule_rec.attribute6,
p_attribute7 => p_schedule_rec.attribute7,
p_attribute8 => p_schedule_rec.attribute8,
p_attribute9 => p_schedule_rec.attribute9,
p_attribute10 => p_schedule_rec.attribute10,
p_attribute11 => p_schedule_rec.attribute11,
p_attribute12 => p_schedule_rec.attribute12,
p_attribute13 => p_schedule_rec.attribute13,
p_attribute14 => p_schedule_rec.attribute14,
p_attribute15 => p_schedule_rec.attribute15,
p_activity_attribute_category => p_schedule_rec.activity_attribute_category,
p_activity_attribute1 => p_schedule_rec.activity_attribute1,
p_activity_attribute2 => p_schedule_rec.activity_attribute2,
p_activity_attribute3 => p_schedule_rec.activity_attribute3,
p_activity_attribute4 => p_schedule_rec.activity_attribute4,
p_activity_attribute5 => p_schedule_rec.activity_attribute5,
p_activity_attribute6 => p_schedule_rec.activity_attribute6,
p_activity_attribute7 => p_schedule_rec.activity_attribute7,
p_activity_attribute8 => p_schedule_rec.activity_attribute8,
p_activity_attribute9 => p_schedule_rec.activity_attribute9,
p_activity_attribute10 => p_schedule_rec.activity_attribute10,
p_activity_attribute11 => p_schedule_rec.activity_attribute11,
p_activity_attribute12 => p_schedule_rec.activity_attribute12,
p_activity_attribute13 => p_schedule_rec.activity_attribute13,
p_activity_attribute14 => p_schedule_rec.activity_attribute14,
p_activity_attribute15 => p_schedule_rec.activity_attribute15,
-- removed by soagrawa on 24-sep-2001
-- p_security_group_id => p_schedule_rec.security_group_id,
p_query_id => p_schedule_rec.query_id,
p_include_content_flag => p_schedule_rec.include_content_flag,
p_content_type => p_schedule_rec.content_type,
p_test_email_address => p_schedule_rec.test_email_address,
p_schedule_name => p_schedule_rec.schedule_name,
p_schedule_description => p_schedule_rec.description,
p_greeting_text => p_schedule_rec.greeting_text,
p_footer_text => p_schedule_rec.footer_text,
--following is added by anchaudh on 27-jun-2003
p_trig_repeat_flag => p_schedule_rec.trig_repeat_flag,
p_tgrp_exclude_prev_flag => p_schedule_rec.tgrp_exclude_prev_flag,
p_orig_csch_id => p_schedule_rec.orig_csch_id,
p_cover_letter_version => p_schedule_rec.cover_letter_version,
-- added by dbiswas on Aug 12, 2003
p_usage => p_schedule_rec.usage,
p_purpose => p_schedule_rec.purpose,
p_last_activation_date => p_schedule_rec.last_activation_date,
p_sales_methodology_id => p_schedule_rec.sales_methodology_id,
p_printer_address => p_schedule_rec.printer_address,
-- added by dbiswas on Jul 27, 2005
p_notify_on_activation_flag => p_schedule_rec.notify_on_activation_flag,
-- added by anchaudh on Feb 01, 2006
p_sender_display_name => p_schedule_rec.sender_display_name,
-- added by srivikri on 29-May-2006
p_delivery_mode => p_schedule_rec.delivery_mode
);
PROCEDURE Update_Camp_Schedule(
p_api_version_number 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_schedule_rec IN schedule_rec_type,
x_object_version_number OUT NOCOPY NUMBER
)
IS
CURSOR c_get_camp_schedule(schedule_id IN NUMBER) IS
SELECT *
FROM ams_campaign_schedules_vl
WHERE schedule_id = p_schedule_rec.schedule_id ;
SELECT status_code, triggerable_flag, trigger_id,
trig_repeat_flag, tgrp_exclude_prev_flag, activity_type_code
FROM ams_campaign_schedules_b
WHERE schedule_id = p_schedule_rec.schedule_id ;
L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Camp_Schedule';
SAVEPOINT UPDATE_Camp_Schedule_PVT;
AMS_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
FND_MESSAGE.Set_Name('AMS', 'API_MISSING_UPDATE_TARGET');
FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
AMS_Utility_PVT.Error_Message('AMS_TRIG_DETAILS_NO_UPDATE');
p_validation_mode => JTF_PLSQL_API.g_update,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
AMS_ScheduleRules_PVT.Check_Schedule_Update(
p_schedule_rec => l_complete_rec,
x_return_status => x_return_status);
AMS_Utility_PVT.debug_message(l_api_name ||': update source code');
AMS_Utility_PVT.debug_message(l_api_name ||': update source code222');
AMS_Utility_PVT.debug_message(l_api_name ||': updated source code '||l_source_code);
AMS_ScheduleRules_PVT.Update_Schedule_Owner(
p_api_version => p_api_version_number,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_object_type => 'CSCH' ,
p_schedule_id => p_schedule_rec.schedule_id,
p_owner_id => p_schedule_rec.owner_user_id
);
AMS_UTILITY_PVT.debug_message('Private API: Calling update table handler sched : '||to_char(p_schedule_rec.schedule_id));
AMS_CAMPAIGN_SCHEDULES_B_PKG.Update_Row(
p_schedule_id => p_schedule_rec.schedule_id,
p_last_update_date => SYSDATE,
p_last_updated_by => G_USER_ID,
p_creation_date => SYSDATE,
p_created_by => G_USER_ID,
p_last_update_login => G_LOGIN_ID,
p_object_version_number => p_schedule_rec.object_version_number ,
p_campaign_id => p_schedule_rec.campaign_id,
p_user_status_id => FND_API.G_MISS_NUM,
p_status_code => FND_API.G_MISS_CHAR,
p_status_date => FND_API.G_MISS_DATE,
-- modified on 12-dec-2001 by soagrawa, bug# 2133264
-- p_source_code => p_schedule_rec.source_code,
p_source_code => l_source_code,
p_use_parent_code_flag => p_schedule_rec.use_parent_code_flag,
-- modified by soagrawa on 26-sep-2001 after changing amstschb.pls
-- p_start_date_time => p_schedule_rec.start_date_time,
-- p_end_date_time => p_schedule_rec.end_date_time,
p_start_date_time => l_complete_rec.start_date_time,
p_end_date_time => l_complete_rec.end_date_time,
p_timezone_id => p_schedule_rec.timezone_id,
p_activity_type_code => p_schedule_rec.activity_type_code,
p_activity_id => p_schedule_rec.activity_id,
p_arc_marketing_medium_from => p_schedule_rec.arc_marketing_medium_from,
p_marketing_medium_id => p_schedule_rec.marketing_medium_id,
p_custom_setup_id => p_schedule_rec.custom_setup_id,
p_triggerable_flag => p_schedule_rec.triggerable_flag,
p_trigger_id => p_schedule_rec.trigger_id,
p_notify_user_id => p_schedule_rec.notify_user_id,
p_approver_user_id => p_schedule_rec.approver_user_id,
p_owner_user_id => p_schedule_rec.owner_user_id,
p_active_flag => p_schedule_rec.active_flag,
-- soagrawa removed on 03-nov-2003 for 11.5.10
p_cover_letter_id => null,
p_reply_to_mail => p_schedule_rec.reply_to_mail,
p_mail_sender_name => p_schedule_rec.mail_sender_name,
p_mail_subject => p_schedule_rec.mail_subject,
p_from_fax_no => p_schedule_rec.from_fax_no,
p_accounts_closed_flag => p_schedule_rec.accounts_closed_flag,
p_org_id => p_schedule_rec.org_id,
p_objective_code => p_schedule_rec.objective_code,
p_country_id => p_schedule_rec.country_id,
p_campaign_calendar => l_tar_schedule_rec.campaign_calendar,
p_start_period_name => p_schedule_rec.start_period_name,
p_end_period_name => p_schedule_rec.end_period_name,
p_priority => p_schedule_rec.priority,
p_workflow_item_key => p_schedule_rec.workflow_item_key,
p_transaction_currency_code => p_schedule_rec.transaction_currency_code,
p_functional_currency_code => l_tar_schedule_rec.functional_currency_code,
p_attribute_category => p_schedule_rec.attribute_category,
p_budget_amount_tc => p_schedule_rec.budget_amount_tc,
p_budget_amount_fc => l_tar_schedule_rec.budget_amount_fc,
p_language_code => p_schedule_rec.language_code,
p_task_id => p_schedule_rec.task_id,
p_related_event_from => p_schedule_rec.related_event_from,
p_related_event_id => p_schedule_rec.related_event_id,
p_attribute1 => p_schedule_rec.attribute1,
p_attribute2 => p_schedule_rec.attribute2,
p_attribute3 => p_schedule_rec.attribute3,
p_attribute4 => p_schedule_rec.attribute4,
p_attribute5 => p_schedule_rec.attribute5,
p_attribute6 => p_schedule_rec.attribute6,
p_attribute7 => p_schedule_rec.attribute7,
p_attribute8 => p_schedule_rec.attribute8,
p_attribute9 => p_schedule_rec.attribute9,
p_attribute10 => p_schedule_rec.attribute10,
p_attribute11 => p_schedule_rec.attribute11,
p_attribute12 => p_schedule_rec.attribute12,
p_attribute13 => p_schedule_rec.attribute13,
p_attribute14 => p_schedule_rec.attribute14,
p_attribute15 => p_schedule_rec.attribute15,
p_activity_attribute_category => p_schedule_rec.activity_attribute_category,
p_activity_attribute1 => p_schedule_rec.activity_attribute1,
p_activity_attribute2 => p_schedule_rec.activity_attribute2,
p_activity_attribute3 => p_schedule_rec.activity_attribute3,
p_activity_attribute4 => p_schedule_rec.activity_attribute4,
p_activity_attribute5 => p_schedule_rec.activity_attribute5,
p_activity_attribute6 => p_schedule_rec.activity_attribute6,
p_activity_attribute7 => p_schedule_rec.activity_attribute7,
p_activity_attribute8 => p_schedule_rec.activity_attribute8,
p_activity_attribute9 => p_schedule_rec.activity_attribute9,
p_activity_attribute10 => p_schedule_rec.activity_attribute10,
p_activity_attribute11 => p_schedule_rec.activity_attribute11,
p_activity_attribute12 => p_schedule_rec.activity_attribute12,
p_activity_attribute13 => p_schedule_rec.activity_attribute13,
p_activity_attribute14 => p_schedule_rec.activity_attribute14,
p_activity_attribute15 => p_schedule_rec.activity_attribute15,
-- removed by soagrawa on 24-sep-2001
-- p_security_group_id => p_schedule_rec.security_group_id,
p_query_id => p_schedule_rec.query_id,
p_include_content_flag => p_schedule_rec.include_content_flag,
p_content_type => p_schedule_rec.content_type,
p_test_email_address => p_schedule_rec.test_email_address,
p_schedule_name => p_schedule_rec.schedule_name,
p_schedule_description => p_schedule_rec.description,
p_greeting_text => p_schedule_rec.greeting_text,
p_footer_text => p_schedule_rec.footer_text,
--following is added by anchaudh on 27-jun-2003
p_trig_repeat_flag => p_schedule_rec.trig_repeat_flag,
p_tgrp_exclude_prev_flag => p_schedule_rec.tgrp_exclude_prev_flag,
p_orig_csch_id => p_schedule_rec.orig_csch_id,
p_cover_letter_version => p_schedule_rec.cover_letter_version,
-- added by dbiswas on Aug 12, 2003
p_usage => p_schedule_rec.usage,
p_purpose => p_schedule_rec.purpose,
p_last_activation_date => p_schedule_rec.last_activation_date,
p_sales_methodology_id => p_schedule_rec.sales_methodology_id,
p_printer_address => p_schedule_rec.printer_address,
-- added by dbiswas on Jul 27, 2005
p_notify_on_activation_flag => p_schedule_rec.notify_on_activation_flag,
-- added by anchaudh on Feb 01, 2006
p_sender_display_name => p_schedule_rec.sender_display_name,
p_delivery_mode => p_schedule_rec.delivery_mode
);
AMS_ScheduleRules_PVT.Update_Schedule_Status(
p_schedule_id => p_schedule_rec.schedule_id,
p_campaign_id => l_complete_rec.campaign_id,
p_user_status_id => l_complete_rec.user_status_id,
p_budget_amount => l_complete_rec.budget_amount_tc,
p_asn_group_id => p_schedule_rec.asn_group_id ); -- anchaudh added for leads bug.
ROLLBACK TO UPDATE_Camp_Schedule_PVT;
ROLLBACK TO UPDATE_Camp_Schedule_PVT;
ROLLBACK TO UPDATE_Camp_Schedule_PVT;
End Update_Camp_Schedule;
PROCEDURE Delete_Camp_Schedule(
p_api_version_number 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_schedule_id IN NUMBER,
p_object_version_number IN NUMBER
)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Camp_Schedule';
SELECT campaign_id, schedule_id, status_code,use_parent_code_flag,source_code
FROM ams_campaign_schedules_b
WHERE schedule_id = p_schedule_id
AND object_version_number = p_object_version_number ;
SELECT 1
FROM ams_campaign_schedules_b
WHERE campaign_id = l_campaign_id
AND active_flag = 'Y' ;
SAVEPOINT DELETE_Camp_Schedule_PVT;
AMS_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
AMS_CAMPAIGN_SCHEDULES_B_PKG.Delete_Row(p_schedule_id => p_schedule_id);
UPDATE ams_campaign_schedules_b
SET object_version_number = object_version_number + 1 ,
active_flag = 'N'
WHERE schedule_id = p_schedule_id
AND object_version_number = p_object_version_number ;
ROLLBACK TO DELETE_Camp_Schedule_PVT;
ROLLBACK TO DELETE_Camp_Schedule_PVT;
ROLLBACK TO DELETE_Camp_Schedule_PVT;
End Delete_Camp_Schedule;
SELECT SCHEDULE_ID
FROM AMS_CAMPAIGN_SCHEDULES_B
WHERE SCHEDULE_ID = p_SCHEDULE_ID
AND object_version_number = p_object_version
FOR UPDATE NOWAIT;
SELECT *
FROM ams_campaign_schedules_vl
WHERE schedule_id = p_schedule_rec.schedule_id;
p_validation_mode => JTF_PLSQL_API.g_update,
x_return_status => x_return_status
);
IF p_validation_mode = JTF_PLSQL_API.g_update THEN
IF (AMS_DEBUG_HIGH_ON) THEN
AMS_Utility_PVT.debug_message('After Item validation');
AMS_Utility_PVT.Error_Message('AMS_SELECT_TRIGGER');
SELECT status_code,rollup_type
FROM ams_campaigns_all_b
WHERE campaign_id = p_schedule_rec.campaign_id ;
SELECT 1 from dual
WHERE EXISTS (SELECT *
FROM ams_campaign_schedules_vl
WHERE campaign_id = p_complete_rec.campaign_id
AND UPPER(schedule_name) = UPPER(p_complete_rec.schedule_name)) ;
SELECT 1 from dual
WHERE EXISTS (SELECT *
FROM ams_campaign_schedules_vl
WHERE campaign_id = p_complete_rec.campaign_id
AND UPPER(schedule_name) = UPPER(p_complete_rec.schedule_name)
AND schedule_id <> p_complete_rec.schedule_id ) ;
IF p_validation_mode = JTF_PLSQL_API.g_update THEN
OPEN c_sch_name_updt;
x_schedule_rec.last_update_date := FND_API.G_MISS_DATE ;
x_schedule_rec.last_updated_by := FND_API.G_MISS_NUM ;
x_schedule_rec.last_update_login := FND_API.G_MISS_NUM;
SELECT campaign_id
FROM ams_campaigns_vl
WHERE campaign_name = l_campaign_name;
SELECT campaign_id
FROM ams_campaigns_vl
WHERE campaign_name = l_campaign_name;
SELECT
schedule_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
null, -- obj version number
campaign_id,
user_status_id,
status_code,
status_date,
source_code,
use_parent_code_flag,
start_date_time,
end_date_time,
timezone_id,
activity_type_code,
activity_id,
arc_marketing_medium_from,
marketing_medium_id,
custom_setup_id,
triggerable_flag,
trigger_id,
notify_user_id,
approver_user_id,
owner_user_id,
active_flag,
cover_letter_id,
reply_to_mail,
mail_sender_name,
mail_subject,
from_fax_no,
accounts_closed_flag,
org_id,
objective_code,
country_id,
campaign_calendar,
start_period_name,
end_period_name,
priority,
workflow_item_key,
transaction_currency_code,
functional_currency_code,
budget_amount_tc,
budget_amount_fc,
language_code,
task_id,
related_event_from,
related_event_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
activity_attribute_category,
activity_attribute1,
activity_attribute2,
activity_attribute3,
activity_attribute4,
activity_attribute5,
activity_attribute6,
activity_attribute7,
activity_attribute8,
activity_attribute9,
activity_attribute10,
activity_attribute11,
activity_attribute12,
activity_attribute13,
activity_attribute14,
activity_attribute15,
security_group_id,
schedule_name,
description,
null,null,NULL --related source stuff
FROM ams_campaign_schedules_vl
WHERE schedule_id = sch_id;
SELECT * FROM ams_campaign_schedules_vl
WHERE schedule_id = sch_id ;
SELECT * FROM ams_event_offers_vl
WHERE event_offer_id = event_id ;
SELECT delivery_media_type_code
FROM ams_act_delivery_methods
WHERE activity_delivery_method_id = delv_id;
SELECT source_code
FROM ams_event_offers_all_b
WHERE event_offer_id = event_id;
AMS_UTILITY_PVT.debug_message('Copy Attributes, usage is LITE and COLT component is selected' );
AMS_UTILITY_PVT.debug_message('Copy Attributes, usage is LITE but COLT component is not selected' );