The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ams_campaigns_all_b_s.NEXTVAL
FROM DUAL;
SELECT COUNT(*)
FROM ams_campaigns_vl
WHERE campaign_id = camp_id;
SELECT business_unit_id
from ams_campaigns_all_b
where campaign_id = p_parent_campaign_id
and exists (
SELECT 1 FROM hr_all_organization_units_vl
WHERE business_group_id IN (
SELECT business_group_id FROM hr_all_organization_units_vl
WHERE organization_id = p_org_id
AND NVL(date_from, SYSDATE) <= SYSDATE
AND NVL(date_to, SYSDATE) >= SYSDATE)
AND type = 'BU'
AND NVL(date_from, SYSDATE) <= SYSDATE
AND NVL(date_to, SYSDATE) >= SYSDATE
AND organization_id = business_unit_id
);
SELECT business_unit_id
from ams_campaigns_all_b
where campaign_id = p_parent_campaign_id
and exists (
SELECT 1
FROM hr_all_organization_units_vl
WHERE business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
AND NVL(date_from, SYSDATE) <= SYSDATE
AND NVL(date_to, SYSDATE) >= SYSDATE
AND type = 'BU'
AND organization_id = business_unit_id);
select private_flag
from ams_campaigns_all_b
where campaign_id = p_parent_campaign_id;
AMS_Utility_PVT.debug_message(l_full_name ||': insert');
INSERT INTO ams_campaigns_all_b(
campaign_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
object_version_number,
owner_user_id,
user_status_id,
status_code,
status_date,
active_flag,
private_flag,
partner_flag,
template_flag,
cascade_source_code_flag,
inherit_attributes_flag,
source_code,
rollup_type,
campaign_type,
media_type_code,
priority,
fund_source_type,
fund_source_id,
parent_campaign_id,
application_id,
qp_list_header_id,
org_id,
media_id,
channel_id,
event_type,
arc_channel_from,
dscript_name,
transaction_currency_code,
functional_currency_code,
budget_amount_tc,
budget_amount_fc,
forecasted_plan_start_date,
forecasted_plan_end_date,
forecasted_exec_start_date,
forecasted_exec_end_date,
actual_plan_start_date,
actual_plan_end_date,
actual_exec_start_date,
actual_exec_end_date,
inbound_url,
inbound_email_id,
inbound_phone_no,
duration,
duration_uom_code,
ff_priority,
ff_override_cover_letter,
ff_shipping_method,
ff_carrier,
content_source,
cc_call_strategy,
cc_manager_user_id,
forecasted_revenue,
actual_revenue,
forecasted_cost,
actual_cost,
forecasted_response,
actual_response,
target_response,
country_code,
language_code,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
version_no,
campaign_calendar,
start_period_name,
end_period_name,
city_id,
global_flag,
custom_setup_id,
show_campaign_flag,
business_unit_id,
accounts_closed_flag,
task_id,
related_event_from,
related_event_id,
program_attribute_category,
program_attribute1,
program_attribute2,
program_attribute3,
program_attribute4,
program_attribute5,
program_attribute6,
program_attribute7,
program_attribute8,
program_attribute9,
program_attribute10,
program_attribute11,
program_attribute12,
program_attribute13,
program_attribute14,
program_attribute15
)
VALUES(
l_camp_rec.campaign_id,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.conc_login_id,
1, -- object_version_number
l_camp_rec.owner_user_id,
l_camp_rec.user_status_id,
l_camp_rec.status_code,
NVL(l_camp_rec.status_date, SYSDATE),
NVL(l_camp_rec.active_flag, 'Y'),
NVL(l_camp_rec.private_flag, 'N'),
NVL(l_camp_rec.partner_flag, 'N'),
NVL(l_camp_rec.template_flag, 'N'),
NVL(l_camp_rec.cascade_source_code_flag, 'N'),
NVL(l_camp_rec.inherit_attributes_flag,'N'),
l_camp_rec.source_code,
l_camp_rec.rollup_type,
l_camp_rec.campaign_type,
l_camp_rec.media_type_code,
NVL(l_camp_rec.priority, 'STANDARD'),
l_camp_rec.fund_source_type,
l_camp_rec.fund_source_id,
l_camp_rec.parent_campaign_id,
l_camp_rec.application_id,
l_camp_rec.qp_list_header_id,
l_org_id, -- org_id value from MO: Default Org Id,
-- commenting out. moac changes.
--TO_NUMBER(SUBSTRB(userenv('CLIENT_INFO'),1,10)), -- org_id
l_camp_rec.media_id,
l_camp_rec.channel_id,
l_camp_rec.event_type,
l_camp_rec.arc_channel_from,
l_camp_rec.dscript_name,
l_camp_rec.transaction_currency_code,
l_camp_rec.functional_currency_code,
l_camp_rec.budget_amount_tc,
l_camp_rec.budget_amount_fc,
l_camp_rec.forecasted_plan_start_date,
l_camp_rec.forecasted_plan_end_date,
l_camp_rec.forecasted_exec_start_date,
l_camp_rec.forecasted_exec_end_date,
l_camp_rec.actual_plan_start_date,
l_camp_rec.actual_plan_end_date,
l_camp_rec.actual_exec_start_date,
l_camp_rec.actual_exec_end_date,
l_camp_rec.inbound_url,
l_camp_rec.inbound_email_id,
l_camp_rec.inbound_phone_no,
l_camp_rec.duration,
l_camp_rec.duration_uom_code,
l_camp_rec.ff_priority,
l_camp_rec.ff_override_cover_letter,
l_camp_rec.ff_shipping_method,
l_camp_rec.ff_carrier,
l_camp_rec.content_source,
l_camp_rec.cc_call_strategy,
l_camp_rec.cc_manager_user_id,
l_camp_rec.forecasted_revenue,
l_camp_rec.actual_revenue,
l_camp_rec.forecasted_cost,
l_camp_rec.actual_cost,
l_camp_rec.forecasted_response,
l_camp_rec.actual_response,
l_camp_rec.target_response,
l_camp_rec.country_code,
l_camp_rec.language_code,
l_camp_rec.attribute_category,
l_camp_rec.attribute1,
l_camp_rec.attribute2,
l_camp_rec.attribute3,
l_camp_rec.attribute4,
l_camp_rec.attribute5,
l_camp_rec.attribute6,
l_camp_rec.attribute7,
l_camp_rec.attribute8,
l_camp_rec.attribute9,
l_camp_rec.attribute10,
l_camp_rec.attribute11,
l_camp_rec.attribute12,
l_camp_rec.attribute13,
l_camp_rec.attribute14,
l_camp_rec.attribute15,
null, -- version_no
l_camp_rec.campaign_calendar,
l_camp_rec.start_period_name,
l_camp_rec.end_period_name,
NVL(l_camp_rec.city_id, TO_NUMBER(FND_PROFILE.value('AMS_SRCGEN_USER_CITY'))),
NVL(l_camp_rec.global_flag, 'N'),
l_camp_rec.custom_setup_id,
NVL(l_camp_rec.show_campaign_flag, 'Y'),
l_camp_rec.business_unit_id,
--07-apr-2003 cgoyal
'N', --NVL(l_camp_rec.accounts_closed_flag,'Y'),
--end 07-apr-2003 cgoyal
l_camp_rec.task_id,
l_camp_rec.related_event_from,
l_camp_rec.related_event_id,
l_camp_rec.program_attribute_category,
l_camp_rec.program_attribute1,
l_camp_rec.program_attribute2,
l_camp_rec.program_attribute3,
l_camp_rec.program_attribute4,
l_camp_rec.program_attribute5,
l_camp_rec.program_attribute6,
l_camp_rec.program_attribute7,
l_camp_rec.program_attribute8,
l_camp_rec.program_attribute9,
l_camp_rec.program_attribute10,
l_camp_rec.program_attribute11,
l_camp_rec.program_attribute12,
l_camp_rec.program_attribute13,
l_camp_rec.program_attribute14,
l_camp_rec.program_attribute15
);
INSERT INTO ams_campaigns_all_tl(
campaign_id,
language,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
source_lang,
campaign_name,
campaign_theme,
description
)
SELECT
l_camp_rec.campaign_id,
l.language_code,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.conc_login_id,
USERENV('LANG'),
l_camp_rec.campaign_name,
l_camp_rec.campaign_theme,
l_camp_rec.description
FROM fnd_languages l
WHERE l.installed_flag in ('I', 'B')
AND NOT EXISTS(
SELECT NULL
FROM ams_campaigns_all_tl t
WHERE t.campaign_id = l_camp_rec.campaign_id
AND t.language = l.language_code );
l_access_rec.delete_flag := 'N' ;
PROCEDURE Delete_Campaign(
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_camp_id IN NUMBER,
p_object_version IN NUMBER
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'delete_campaign';
SELECT status_code
FROM ams_campaigns_all_b
WHERE campaign_id = p_camp_id
AND object_version_number = p_object_version ;
SAVEPOINT delete_campaign;
AMS_Utility_PVT.debug_message(l_full_name ||': delete');
DELETE FROM ams_campaigns_all_b
WHERE campaign_id = p_camp_id
AND object_version_number = p_object_version ;
UPDATE ams_campaigns_all_b
SET active_flag = 'N'
WHERE campaign_id = p_camp_id
AND object_version_number = p_object_version;
ROLLBACK TO delete_campaign;
ROLLBACK TO delete_campaign;
ROLLBACK TO delete_campaign;
END Delete_Campaign;
SELECT campaign_id
FROM ams_campaigns_all_b
WHERE campaign_id = p_camp_id
AND object_version_number = p_object_version
FOR UPDATE NOWAIT;
SELECT campaign_id
FROM ams_campaigns_all_tl
WHERE campaign_id = p_camp_id
AND USERENV('LANG') IN (language, source_lang)
FOR UPDATE NOWAIT;
PROCEDURE Update_Campaign(
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_camp_rec IN camp_rec_type
)
IS
l_source_code_for_event VARCHAR2(30);
l_api_name CONSTANT VARCHAR2(30) := 'update_campaign';
SAVEPOINT update_campaign;
p_validation_mode => JTF_PLSQL_API.g_update,
x_return_status => l_return_status
);
p_validation_mode => JTF_PLSQL_API.g_update,
x_return_status => l_return_status
);
AMS_Utility_PVT.debug_message(l_full_name ||': check update');
AMS_CampaignRules_PVT.check_camp_update(
p_camp_rec => p_camp_rec,
p_complete_rec => l_camp_rec,
x_return_status => l_return_status
);
AMS_Utility_PVT.debug_message(l_full_name ||': update source code');
/*AMS_CampaignRules_PVT.update_camp_source_code(
l_camp_rec.campaign_id,
l_camp_rec.source_code,
l_camp_rec.global_flag,
l_camp_rec.source_code,
l_return_status
);*/
AMS_Utility_PVT.Debug_message('Update the source_code ');
AMS_CampaignRules_PVT.update_camp_source_code(
l_camp_rec.campaign_id,
l_camp_rec.source_code,
l_camp_rec.global_flag,
--l_camp_rec.source_code,
l_source_code,
l_camp_rec.related_event_from,
l_camp_rec.related_event_id,
l_return_status
);
AMS_CampaignRules_PVT.Update_Owner(
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_object_type => l_rollup_type ,
p_campaign_id => l_camp_rec.campaign_id,
p_owner_id => p_camp_rec.owner_user_id
);
AMS_CampaignRules_PVT.Update_Rollup(
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_campaign_id => l_camp_rec.campaign_id,
p_parent_id => p_camp_rec.parent_Campaign_id
);
AMS_Utility_PVT.debug_message(l_full_name ||': update');
UPDATE ams_campaigns_all_b SET
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.conc_login_id,
object_version_number = l_camp_rec.object_version_number + 1,
owner_user_id = l_camp_rec.owner_user_id,
active_flag = NVL(l_camp_rec.active_flag, 'Y'),
private_flag = NVL(l_camp_rec.private_flag, 'N'),
partner_flag = NVL(l_camp_rec.partner_flag, 'N'),
template_flag = NVL(l_camp_rec.template_flag, 'N'),
cascade_source_code_flag = NVL(l_camp_rec.cascade_source_code_flag, 'N'),
inherit_attributes_flag = NVL(l_camp_rec.inherit_attributes_flag,'N'),
source_code = l_camp_rec.source_code,
rollup_type = l_camp_rec.rollup_type,
campaign_type = l_camp_rec.campaign_type,
media_type_code = l_camp_rec.media_type_code,
priority = l_camp_rec.priority,
fund_source_type = l_camp_rec.fund_source_type,
fund_source_id = l_camp_rec.fund_source_id,
parent_campaign_id = l_camp_rec.parent_campaign_id,
application_id = l_camp_rec.application_id,
qp_list_header_id = l_camp_rec.qp_list_header_id,
media_id = l_camp_rec.media_id,
channel_id = l_camp_rec.channel_id,
event_type = l_camp_rec.event_type,
arc_channel_from = l_camp_rec.arc_channel_from,
dscript_name = l_camp_rec.dscript_name,
transaction_currency_code = l_camp_rec.transaction_currency_code,
functional_currency_code = l_camp_rec.functional_currency_code,
budget_amount_tc = l_camp_rec.budget_amount_tc,
budget_amount_fc = l_camp_rec.budget_amount_fc,
forecasted_plan_start_date = l_camp_rec.forecasted_plan_start_date,
forecasted_plan_end_date = l_camp_rec.forecasted_plan_end_date,
forecasted_exec_start_date = l_camp_rec.forecasted_exec_start_date,
forecasted_exec_end_date = l_camp_rec.forecasted_exec_end_date,
actual_plan_start_date = l_camp_rec.actual_plan_start_date,
actual_plan_end_date = l_camp_rec.actual_plan_end_date,
actual_exec_start_date = l_camp_rec.actual_exec_start_date,
actual_exec_end_date = l_camp_rec.actual_exec_end_date,
inbound_url = l_camp_rec.inbound_url,
inbound_email_id = l_camp_rec.inbound_email_id,
inbound_phone_no = l_camp_rec.inbound_phone_no,
duration = l_camp_rec.duration,
duration_uom_code = l_camp_rec.duration_uom_code,
ff_priority = l_camp_rec.ff_priority,
ff_override_cover_letter = l_camp_rec.ff_override_cover_letter,
ff_shipping_method = l_camp_rec.ff_shipping_method,
ff_carrier = l_camp_rec.ff_carrier,
content_source = l_camp_rec.content_source,
cc_call_strategy = l_camp_rec.cc_call_strategy,
cc_manager_user_id = l_camp_rec.cc_manager_user_id,
forecasted_revenue = l_camp_rec.forecasted_revenue,
actual_revenue = l_camp_rec.actual_revenue,
forecasted_cost = l_camp_rec.forecasted_cost,
actual_cost = l_camp_rec.actual_cost,
forecasted_response = l_camp_rec.forecasted_response,
actual_response = l_camp_rec.actual_response,
target_response = l_camp_rec.target_response,
country_code = l_camp_rec.country_code,
language_code = l_camp_rec.language_code,
attribute_category = l_camp_rec.attribute_category,
attribute1 = l_camp_rec.attribute1,
attribute2 = l_camp_rec.attribute2,
attribute3 = l_camp_rec.attribute3,
attribute4 = l_camp_rec.attribute4,
attribute5 = l_camp_rec.attribute5,
attribute6 = l_camp_rec.attribute6,
attribute7 = l_camp_rec.attribute7,
attribute8 = l_camp_rec.attribute8,
attribute9 = l_camp_rec.attribute9,
attribute10 = l_camp_rec.attribute10,
attribute11 = l_camp_rec.attribute11,
attribute12 = l_camp_rec.attribute12,
attribute13 = l_camp_rec.attribute13,
attribute14 = l_camp_rec.attribute14,
attribute15 = l_camp_rec.attribute15,
--version_no = l_camp_rec.version_no,
campaign_calendar = l_camp_rec.campaign_calendar,
start_period_name = l_camp_rec.start_period_name,
end_period_name = l_camp_rec.end_period_name,
city_id = l_camp_rec.city_id,
global_flag = NVL(l_camp_rec.global_flag, 'N'),
custom_setup_id = l_camp_rec.custom_setup_id,
show_campaign_flag = NVL(l_camp_rec.show_campaign_flag, 'Y'),
business_unit_id = l_camp_rec.business_unit_id,
program_attribute_category = l_camp_rec.program_attribute_category,
program_attribute1 = l_camp_rec.program_attribute1,
program_attribute2 = l_camp_rec.program_attribute2,
program_attribute3 = l_camp_rec.program_attribute3,
program_attribute4 = l_camp_rec.program_attribute4,
program_attribute5 = l_camp_rec.program_attribute5,
program_attribute6 = l_camp_rec.program_attribute6,
program_attribute7 = l_camp_rec.program_attribute7,
program_attribute8 = l_camp_rec.program_attribute8,
program_attribute9 = l_camp_rec.program_attribute9,
program_attribute10 = l_camp_rec.program_attribute10,
program_attribute11 = l_camp_rec.program_attribute11,
program_attribute12 = l_camp_rec.program_attribute12,
program_attribute13 = l_camp_rec.program_attribute13,
program_attribute14 = l_camp_rec.program_attribute14,
program_attribute15 = l_camp_rec.program_attribute15
--Added by rrajesh on 04/13/01 - to update related_event_fields
,related_event_id = l_camp_rec.related_event_id
,related_event_from = l_camp_rec.related_event_from
--end change 04/13/01 - related event
WHERE campaign_id = l_camp_rec.campaign_id
AND object_version_number = l_camp_rec.object_version_number;
UPDATE ams_campaigns_all_tl SET
campaign_name = l_camp_rec.campaign_name,
campaign_theme = l_camp_rec.campaign_theme,
description = l_camp_rec.description,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.conc_login_id,
source_lang = USERENV('LANG')
WHERE campaign_id = l_camp_rec.campaign_id
AND USERENV('LANG') IN (language, source_lang);
AMS_CampaignRules_PVT.update_camp_status(
l_camp_rec.campaign_id,
l_camp_rec.user_status_id,
l_camp_rec.budget_amount_tc,
l_camp_rec.parent_campaign_id
);
ROLLBACK TO update_campaign;
ROLLBACK TO update_campaign;
ROLLBACK TO update_campaign;
END Update_Campaign;
SELECT rollup_type,status_code
FROM ams_campaigns_all_b
WHERE campaign_id = p_camp_rec.parent_campaign_id;
IS SELECT 1 from dual
WHERE EXISTS ( SELECT * from ams_campaigns_vl
WHERE rollup_type = 'RCAM'
AND UPPER(campaign_name) = UPPER(p_complete_rec.campaign_name)) ;
IS SELECT 1 from dual
WHERE EXISTS ( SELECT * from ams_campaigns_vl
WHERE rollup_type = 'RCAM'
AND UPPER(campaign_name) = UPPER(p_complete_rec.campaign_name)
AND campaign_id <> p_complete_rec.campaign_id );
IF p_validation_mode = JTF_PLSQL_API.g_update AND
p_complete_rec.rollup_type = 'RCAM'
THEN
AMS_CampaignRules_PVT.check_camp_dates_vs_child(
p_complete_rec.campaign_id,
p_complete_rec.actual_exec_start_date,
p_complete_rec.actual_exec_end_date,
l_return_status
);
IF p_validation_mode = JTF_PLSQL_API.g_update
AND p_complete_rec.rollup_type <> 'RCAM'
THEN
AMS_CampaignRules_PVT.check_camp_dates_vs_csch(
p_complete_rec.campaign_id,
p_complete_rec.actual_exec_start_date,
p_complete_rec.actual_exec_end_date,
l_return_status
);
IF p_validation_mode = JTF_PLSQL_API.g_update THEN
IF p_complete_rec.rollup_type = 'RCAM' THEN
AMS_CampaignRules_PVT.Check_Prog_Dates_Vs_Eveh(
p_complete_rec.campaign_id,
p_complete_rec.actual_exec_start_date,
p_complete_rec.actual_exec_end_date,
l_return_status
);
x_camp_rec.last_update_date := FND_API.g_miss_date;
x_camp_rec.last_updated_by := FND_API.g_miss_num;
x_camp_rec.last_update_login := FND_API.g_miss_num;
SELECT *
FROM ams_campaigns_vl
WHERE campaign_id = p_camp_rec.campaign_id;