The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_Metrics (
p_evo_rec IN evo_rec_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2
);
l_sql := 'SELECT count(*) FROM ' || l_table_name;
l_sql := 'SELECT count(*) FROM ' || l_table_name || ' WHERE ' || l_type_col || ' = :b2 AND ';
PROCEDURE check_evo_update(
p_evo_rec IN OUT NOCOPY AMS_EVENTOFFER_PVT.evo_rec_type,
x_return_status OUT NOCOPY VARCHAR2
);
SELECT resource_id
FROM ams_jtf_rs_emp_v
WHERE user_id = l_user_id;
SELECT ams_event_offers_all_b_s.NEXTVAL
FROM DUAL;
SELECT count(*)
FROM ams_event_offers_all_b
WHERE event_offer_id = evo_id;
SELECT system_status_code
FROM ams_user_statuses_b
WHERE user_status_id = ust_id
AND system_status_type = 'AMS_EVENT_AGENDA_STATUS';
SELECT count(*) FROM ams_custom_setup_attr
WHERE custom_setup_id = id_in
AND object_attribute = 'ILST'
AND ATTR_AVAILABLE_FLAG = 'Y';
IF AMS_ACCESS_PVT.check_update_access(l_evo_rec.parent_event_offer_id, l_evo_rec.event_object_type, l_res_id, 'USER') = 'N' then
IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
FND_MESSAGE.set_name('AMS', 'AMS_EVO_NO_UPDATE_ACCESS');-- reusing the message
AMS_Utility_PVT.debug_message(l_full_name ||': insert');
insert into ams_event_offers_all_b(
event_offer_id
,setup_type_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,object_version_number
,application_id
,event_header_id
,private_flag
,active_flag
,source_code
,event_level
,user_status_id
,last_status_date
,system_status_code
,event_type_code
,event_delivery_method_id
,event_required_flag
,event_language_code
,event_location_id
,overflow_flag
,partner_flag
,event_standalone_flag
,reg_frozen_flag
,reg_required_flag
,reg_charge_flag
,reg_invited_only_flag
,reg_waitlist_allowed_flag
,reg_overbook_allowed_flag
,parent_event_offer_id
,event_duration
,event_duration_uom_code
,event_start_date
,event_start_date_time
,event_end_date
,event_end_date_time
,reg_start_date
,reg_start_time
,reg_end_date
,reg_end_time
,reg_maximum_capacity
,reg_overbook_pct
,reg_effective_capacity
,reg_waitlist_pct
,reg_minimum_capacity
,reg_minimum_req_by_date
,inventory_item_id
,organization_id
,pricelist_header_id
,pricelist_line_id
,org_id
,waitlist_action_type_code
,stream_type_code
,owner_user_id
,event_full_flag
,forecasted_revenue
,actual_revenue
,forecasted_cost
,actual_cost
,fund_source_type_code
,fund_source_id
,cert_credit_type_code
,certification_credits
,coordinator_id
,priority_type_code
,cancellation_reason_code
,auto_register_flag
,email
,phone
,fund_amount_tc
,fund_amount_fc
,currency_code_tc
,currency_code_fc
,url
,timezone_id
,event_venue_id
,inbound_script_name
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,country_code
,business_unit_id
,event_calendar
,start_period_name
,end_period_name
,global_flag
,task_id -- Hornet
--,program_id -- Hornet
,parent_type -- Hornet
,parent_id -- Hornet
,CREATE_ATTENDANT_LEAD_FLAG --hornet
,CREATE_REGISTRANT_LEAD_FLAG --hornet
,event_object_type --hornet
,reg_timezone_id -- hornet
,event_password /* Hornet : added for imeeting integration*/
,record_event_flag /* Hornet : added for imeeting integration*/
,allow_register_in_middle_flag /* Hornet : added for imeeting integration*/
,publish_attendees_flag /* Hornet : added for imeeting integration*/
,direct_join_flag /* Hornet : added for imeeting integration*/
,event_notification_method /* Hornet : added for imeeting integration*/
,actual_start_time /* Hornet : added for imeeting integration*/
,actual_end_time /* Hornet : added for imeeting integration*/
,server_id /* Hornet : added for imeeting integration*/
,OWNER_FND_USER_ID
,MEETING_DIAL_IN_INFO
,MEETING_EMAIL_SUBJECT
,MEETING_SCHEDULE_TYPE
, MEETING_STATUS
,PUBLISH_FLAG
,MEETING_ENCRYPTION_KEY_CODE
,MEETING_MISC_INFO
,NUMBER_OF_ATTENDEES
,EVENT_PURPOSE_CODE
)
VALUES(
l_evo_rec.event_offer_id,
l_evo_rec.custom_setup_id,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.conc_login_id,
l_ovn, -- object_version_number Bug 5171873 can be 2 for copy
l_evo_rec.application_id,
l_evo_rec.event_header_id, -- will come from Interface
NVL(l_evo_rec.private_flag,'N'), -- Value will come from the User Interface.
NVL(l_evo_rec.active_flag, 'Y'), -- 'N' when active_flag is NULL
l_evo_rec.source_code, -- If the incoming value is NULL, it must be generated.
l_evo_rec.event_level, -- MAIN (event offer), SUB (agenda item)
l_evo_rec.user_status_id,
NVL(l_evo_rec.last_status_date,SYSDATE),
l_evo_rec.system_status_code,
l_evo_rec.event_type_code,
l_dlv_id,
NVL(l_evo_rec.event_required_flag,'N'),
l_evo_rec.event_language_code,
l_evo_rec.event_location_id,
NVL(l_evo_rec.overflow_flag,'N'),
NVL(l_evo_rec.partner_flag,'N'),
NVL(l_evo_rec.event_standalone_flag,'N'), -- Value will come from the User Interface.
NVL(l_evo_rec.reg_frozen_flag,'N'),
NVL(l_evo_rec.reg_required_flag,'Y'), -- Value will come from the User Interface.
NVL(l_evo_rec.reg_charge_flag,'Y'), -- Value will come from the UserInterface.
NVL(l_evo_rec.reg_invited_only_flag,'N'), -- Value will come from the User Interface.
NVL(l_evo_rec.reg_waitlist_allowed_flag,'N'),
NVL(l_evo_rec.reg_overbook_allowed_flag,'N'),
l_evo_rec.parent_event_offer_id,
l_evo_rec.event_duration,
l_evo_rec.event_duration_uom_code,
l_evo_rec.event_start_date,
NVL(l_evo_rec.event_start_date_time,l_evo_rec.event_start_date),
l_evo_rec.event_end_date,
NVL(l_evo_rec.event_end_date_time,l_evo_rec.event_end_date),
l_evo_rec.reg_start_date,
l_reg_start_time,
l_evo_rec.reg_end_date,
l_reg_end_time,
l_evo_rec.reg_maximum_capacity,
l_evo_rec.reg_overbook_pct,
l_evo_rec.reg_effective_capacity,
l_evo_rec.reg_waitlist_pct,
l_evo_rec.reg_minimum_capacity,
l_evo_rec.reg_minimum_req_by_date,
null, -- l_evo_rec.inventory_item_id, in fact, I should be getting inv item id here as well if evo_rec.inv_num is not null... todo
l_org_id,
l_evo_rec.pricelist_header_id,
l_evo_rec.pricelist_line_id,
--TO_NUMBER(SUBSTRB(userenv('CLIENT_INFO'),1,10)), -- org_id
l_ou_id,
l_evo_rec.waitlist_action_type_code,
l_evo_rec.stream_type_code,
l_evo_rec.owner_user_id,
NVL(l_evo_rec.event_full_flag,'N'),
l_evo_rec.forecasted_revenue,
l_evo_rec.actual_revenue,
l_evo_rec.forecasted_cost,
l_evo_rec.actual_cost,
l_evo_rec.fund_source_type_code,
l_evo_rec.fund_source_id,
l_evo_rec.cert_credit_type_code,
l_evo_rec.certification_credits,
l_evo_rec.coordinator_id,
l_evo_rec.priority_type_code,
l_evo_rec.cancellation_reason_code,
NVL(l_evo_rec.auto_register_flag, nvl(FND_PROFILE.value('AMS_AUTO_REGISTER_FLAG'), 'Y')),
l_evo_rec.email,
l_evo_rec.phone,
l_evo_rec.fund_amount_tc,
l_evo_rec.fund_amount_fc,
l_evo_rec.currency_code_tc,
l_evo_rec.currency_code_fc,
l_evo_rec.url,
l_evo_rec.timezone_id,
l_evo_rec.event_venue_id,
l_evo_rec.inbound_script_name,
l_evo_rec.attribute_category,
l_evo_rec.attribute1,
l_evo_rec.attribute2,
l_evo_rec.attribute3,
l_evo_rec.attribute4,
l_evo_rec.attribute5,
l_evo_rec.attribute6,
l_evo_rec.attribute7,
l_evo_rec.attribute8,
l_evo_rec.attribute9,
l_evo_rec.attribute10,
l_evo_rec.attribute11,
l_evo_rec.attribute12,
l_evo_rec.attribute13,
l_evo_rec.attribute14,
l_evo_rec.attribute15,
-- l_evo_rec.country_code,
-- The above will require the JSP to send the country_code as part of the rec.
-- This is not needed the API can pick it up as follows:
NVL(l_evo_rec.country_code, TO_NUMBER(FND_PROFILE.value('AMS_SRCGEN_USER_CITY'))),
-- The above picks up the country code from the Profile option if the one sent in
-- by the JSP page is null.
l_evo_rec.business_unit_id,
-- The JSPs are expected to send the value of the business_unit_id. It is nullable.
l_evo_rec.event_calendar,
l_evo_rec.start_period_name,
l_evo_rec.end_period_name,
nvl(l_evo_rec.global_flag, 'N'),
-- above 4 fields added to be in synch with campaigns
l_evo_rec.task_id, -- Hornet
--l_evo_rec.program_id, -- Hornet
l_evo_rec.parent_type, -- Hornet
l_evo_rec.parent_id -- Hornet
,l_evo_rec.CREATE_ATTENDANT_LEAD_FLAG --hornet
,l_evo_rec.CREATE_REGISTRANT_LEAD_FLAG --hornet
,l_evo_rec.event_object_type
,l_evo_rec.reg_timezone_id
,l_evo_rec.event_password /* Hornet : added for imeeting integration*/
,l_evo_rec.record_event_flag /* Hornet : added for imeeting integration*/
,l_evo_rec.allow_register_in_middle_flag /* Hornet : added for imeeting integration*/
,l_evo_rec.publish_attendees_flag /* Hornet : added for imeeting integration*/
,l_evo_rec.direct_join_flag /* Hornet : added for imeeting integration*/
,l_evo_rec.event_notification_method /* Hornet : added for imeeting integration*/
,l_evo_rec.actual_start_time /* Hornet : added for imeeting integration*/
,l_evo_rec.actual_end_time /* Hornet : added for imeeting integration*/
,l_evo_rec.server_id /* Hornet : added for imeeting integration*/
,l_evo_rec.OWNER_FND_USER_ID /* Hornet : added for imeeting integration*/
,l_evo_rec.MEETING_DIAL_IN_INFO /* Hornet : added for imeeting integration*/
,l_evo_rec.MEETING_EMAIL_SUBJECT /* Hornet : added for imeeting integration*/
,l_evo_rec.MEETING_SCHEDULE_TYPE /* Hornet : added for imeeting integration*/
,l_evo_rec.MEETING_STATUS /* Hornet : added for imeeting integration*/
,l_evo_rec.PUBLISH_FLAG /* Hornet : added for imeeting integration*/
,l_evo_rec.MEETING_ENCRYPTION_KEY_CODE /* Hornet : added for imeeting integration*/
,l_evo_rec.MEETING_MISC_INFO /* Hornet : added for imeeting integration*/
,l_evo_rec.NUMBER_OF_ATTENDEES /* Hornet : added for imeeting integration*/
,l_evo_rec.EVENT_PURPOSE_CODE /* Hornet */
);
INSERT INTO ams_event_offers_all_tl(
event_offer_id,
language,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
source_lang,
event_offer_name,
event_mktg_message,
description
)
SELECT
l_evo_rec.event_offer_id,
l.language_code,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.conc_login_id,
USERENV('LANG'),
l_evo_rec.event_offer_name,
l_evo_rec.event_mktg_message,
l_evo_rec.description
FROM fnd_languages l
WHERE l.installed_flag in ('I', 'B')
AND NOT EXISTS(
SELECT NULL
FROM ams_event_offers_all_tl t
WHERE t.event_offer_id = l_evo_rec.event_offer_id
AND t.language = l.language_code );
AMS_EvhRules_PVT.Add_Update_Access_record(p_object_type => l_evo_rec.event_object_type, -- 'EVEO'
p_object_id => l_evo_rec.event_offer_id,
p_Owner_user_id => l_evo_rec.owner_user_id,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
PROCEDURE delete_event_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_evo_id IN NUMBER,
p_object_version IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'delete_event_offer';
SELECT event_object_type
FROM AMS_EVENT_OFFERS_ALL_B
WHERE EVENT_OFFER_ID = id_in;
SELECT resource_id
FROM ams_jtf_rs_emp_v
WHERE user_id = l_user_id;
SELECT event_level,parent_event_offer_id
FROM ams_event_offers_all_b
WHERE event_offer_id = l_evo_id
and event_level = 'SUB';
SAVEPOINT delete_event_offer;
if AMS_ACCESS_PVT.check_update_access(l_offer_id, l_object_type, l_res_id, 'USER') = 'N' then
IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
FND_MESSAGE.set_name('AMS', 'AMS_EVO_NO_UPDATE_ACCESS'); --reusing the message
AMS_Utility_PVT.debug_message(l_full_name ||': delete');
UPDATE ams_event_offers_all_b
SET active_flag = 'N'
WHERE event_offer_id = p_evo_id
AND object_version_number = p_object_version;
ROLLBACK TO delete_event_offer;
ROLLBACK TO delete_event_offer;
ROLLBACK TO delete_event_offer;
END delete_event_offer;
SELECT event_offer_id
FROM ams_event_offers_all_b
WHERE event_offer_id = p_evo_id
AND object_version_number = p_object_version
FOR UPDATE OF event_offer_id NOWAIT;
SELECT event_offer_id
FROM ams_event_offers_all_tl
WHERE event_offer_id = p_evo_id
AND USERENV('LANG') IN (language, source_lang)
FOR UPDATE OF event_offer_id NOWAIT;
PROCEDURE update_event_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,
p_evo_rec IN evo_rec_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'update_event_offer';
SELECT system_status_code
FROM ams_user_statuses_b
WHERE user_status_id = p_evo_rec.user_status_id
AND system_status_type = 'AMS_EVENT_STATUS';
SELECT system_status_code
FROM ams_user_statuses_b
WHERE user_status_id = p_evo_rec.user_status_id
AND system_status_type = 'AMS_EVENT_AGENDA_STATUS';
SELECT system_status_code
FROM ams_user_statuses_b
WHERE user_status_id = l_evo_rec.user_status_id --p_evo_rec.user_status_id
AND system_status_type = 'AMS_EVENT_STATUS';
SELECT dlv.delivery_media_type_code, dlv.activity_delivery_method_id, dlv.object_version_number
FROM Ams_act_delivery_methods dlv, ams_event_offers_all_b off
WHERE dlv.activity_delivery_method_id = off.event_delivery_method_id
and off.event_offer_id = p_evo_rec.event_offer_id;
SELECT distinct(pricelist_header_id)
FROM ams_event_offers_all_b evo, qp_price_lists_v qph
WHERE evo.pricelist_header_id = qph.price_list_id
AND qph.currency_code = curr_code;
SELECT resource_id
FROM ams_jtf_rs_emp_v
WHERE user_id = l_user_id;
SELECT loc.address1, loc.address2, loc.city, loc.state, loc.country
FROM hz_locations loc, ams_event_offers_all_b evo
WHERE loc.location_id = evo.event_location_id
and evo.event_offer_id = id_in;
SELECT count(*)
FROM OZF_ACT_BUDGETS --anchaudh: changed call from ams_act_budgets to ozf_act_budgets : bug#3453430
WHERE arc_act_budget_used_by = objtype_in
AND act_budget_used_by_id =id_in;
SELECT global_flag,source_code
FROM ams_event_offers_all_b
WHERE event_offer_id = p_evo_rec.event_offer_id;
SELECT activity_resource_id, object_version_number
FROM ams_act_resources
WHERE act_resource_used_by_id = p_evo_rec.event_offer_id;
SELECT event_venue_id, event_start_date, event_end_date, system_status_code
FROM ams_event_offers_all_b
WHERE event_offer_id = p_evo_rec.event_offer_id;
SELECT event_start_date_time, event_end_date_time
FROM ams_event_offers_all_b
WHERE event_offer_id = p_evo_rec.event_offer_id;
SELECT activity_resource_id, object_version_number
FROM ams_act_resources
WHERE act_resource_used_by_id = id_in;
SELECT activity_resource_id, object_version_number
FROM ams_act_resources
WHERE arc_act_resource_used_by = 'SESSION'
AND act_resource_used_by_id IN ( SELECT agenda_id
FROM ams_agendas_b
WHERE agenda_type = 'SESSION'
AND active_flag = 'Y'
AND parent_id IN ( SELECT agenda_id
FROM ams_agendas_b
WHERE parent_id = id_in ) );
select min(s.start_date_time), max(s.end_date_time)
from ams_agendas_b s, ams_agendas_b h
where s.agenda_type = 'SESSION'
and s.active_flag = 'Y'
and s.parent_id = h.agenda_id
and h.parent_id = id_in
and h.parent_type = type_in;
AMS_Utility_PVT.debug_message(l_full_name||': entered update');
SAVEPOINT update_event_offer;
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 level');
check_evo_update(
p_evo_rec => l_evo_rec,
x_return_status => l_return_status
);
AMS_Utility_PVT.debug_message(l_full_name ||': update source code');
AMS_EvhRules_PVT.update_evo_source_code(
l_evo_rec.event_offer_id,
l_evo_rec.source_code,
l_evo_rec.global_flag,
l_dummy_source_code,
l_return_status
);
AMS_EvhRules_PVT.update_eone_source_code(
l_evo_rec.event_offer_id,
l_evo_rec.source_code,
l_evo_rec.global_flag,
l_dummy_source_code,
l_return_status
);
FND_MESSAGE.set_name('AMS', 'AMS_CAMP_UPDATE_SRC_STAT');
AMS_Utility_PVT.debug_message('Entered AMS_EvhRules_PVT.update_evo_source_code');
AMS_EvhRules_PVT.update_evo_source_code(
l_evo_rec.event_offer_id,
l_evo_rec.source_code,
l_evo_rec.global_flag,
l_dummy_source_code,
l_return_status
);
AMS_EvhRules_PVT.update_eone_source_code(
l_evo_rec.event_offer_id,
l_evo_rec.source_code,
l_evo_rec.global_flag,
l_dummy_source_code,
l_return_status
);
FND_MESSAGE.set_name('AMS', 'AMS_EVNT_UPDATE_GFLG_STAT');
if AMS_ACCESS_PVT.check_update_access(l_evo_id, l_evo_rec.event_object_type, l_res_id, 'USER') = 'N' then
IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
FND_MESSAGE.set_name('AMS', 'AMS_EVO_NO_UPDATE_ACCESS');
AMS_ActDelvMethod_PVT.Update_Act_DelvMethod(
p_api_version => l_api_version,
p_init_msg_list => FND_API.g_false,
p_commit => FND_API.g_false,
p_validation_level => FND_API.g_valid_level_full,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_act_DelvMethod_rec => l_dlv_rec);
AMS_ActDelvMethod_PVT.Delete_Act_DelvMethod(
p_api_version => l_api_version,
p_init_msg_list => FND_API.g_false,
p_commit => FND_API.g_false,
p_validation_level => FND_API.g_valid_level_full,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_act_DelvMethod_id => l_dlv_id,
p_object_version => l_dlv_ver);
Update_Metrics (
p_evo_rec => l_evo_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data =>x_msg_data
);
/* If the owner user id cahnges call AMS_EvhRules_PVT.Update_Owner */
-- Change the owner in Access table if the owner is changed.
IF p_evo_rec.owner_user_id <> FND_API.g_miss_num
THEN
AMS_EvhRules_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_evo_rec.event_object_type,
p_event_id => l_evo_rec.event_offer_id,
p_owner_id => p_evo_rec.owner_user_id
);
UPDATE ams_act_resources
SET object_version_number = l_obj_ver + 1,
system_status_code = 'UNCONFIRMED',
user_status_id = ( SELECT user_status_id
FROM AMS_USER_STATUSES_B
WHERE SYSTEM_STATUS_CODE = 'UNCONFIRMED'
AND SYSTEM_STATUS_TYPE = 'AMS_EVENT_AGENDA_STATUS'
-- added by soagrawa on 25-feb-2003 for bug# 2820297
AND DEFAULT_FLAG = 'Y')
WHERE activity_resource_id = l_actres_id;
UPDATE ams_act_resources
SET object_version_number = l_obj_ver + 1,
system_status_code = 'UNCONFIRMED',
user_status_id = ( SELECT user_status_id
FROM AMS_USER_STATUSES_B
WHERE SYSTEM_STATUS_CODE = 'UNCONFIRMED'
AND SYSTEM_STATUS_TYPE = 'AMS_EVENT_AGENDA_STATUS'
-- added by soagrawa on 25-feb-2003 for bug# 2820297
AND DEFAULT_FLAG = 'Y')
WHERE activity_resource_id = l_actres_id;
update
*/
/*
OPEN c_venue_id;
AMS_Utility_PVT.debug_message(l_full_name ||': update');
UPDATE ams_event_offers_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_evo_rec.object_version_number + 1,
private_flag = l_evo_rec.private_flag,
active_flag = l_evo_rec.active_flag,
source_code = l_evo_rec.source_code,
event_level = l_evo_rec.event_level,
event_type_code = l_evo_rec.event_type_code,
event_delivery_method_id = l_dlv_id,
event_language_code = l_evo_rec.event_language_code,
event_location_id = l_evo_rec.event_location_id,
overflow_flag = l_evo_rec.overflow_flag,
partner_flag = l_evo_rec.partner_flag,
event_standalone_flag = l_evo_rec.event_standalone_flag,
reg_frozen_flag = l_evo_rec.reg_frozen_flag,
reg_required_flag = l_evo_rec.reg_required_flag,
reg_charge_flag = l_evo_rec.reg_charge_flag,
reg_invited_only_flag = l_evo_rec.reg_invited_only_flag,
reg_waitlist_allowed_flag = l_evo_rec.reg_waitlist_allowed_flag,
reg_overbook_allowed_flag = l_evo_rec.reg_overbook_allowed_flag,
parent_event_offer_id = l_evo_rec.parent_event_offer_id,
event_duration = l_evo_rec.event_duration,
event_duration_uom_code = l_evo_rec.event_duration_uom_code,
-- event_start_date = l_evo_rec.event_start_date,
event_start_date_time = l_evo_rec.event_start_date_time,
-- event_end_date = l_evo_rec.event_end_date,
event_end_date_time = l_evo_rec.event_end_date_time,
reg_start_date = l_evo_rec.reg_start_date,
reg_start_time = to_date(to_char(l_evo_rec.reg_start_time,'HH24:MI'),'HH24:MI'),
reg_end_date = l_evo_rec.reg_end_date,
reg_end_time = to_date(to_char(l_evo_rec.reg_end_time,'HH24:MI'),'HH24:MI'),
reg_maximum_capacity = l_evo_rec.reg_maximum_capacity,
reg_overbook_pct = l_evo_rec.reg_overbook_pct,
reg_effective_capacity = l_evo_rec.reg_effective_capacity,
reg_waitlist_pct = l_evo_rec.reg_waitlist_pct,
reg_minimum_capacity = l_evo_rec.reg_minimum_capacity,
reg_minimum_req_by_date = l_evo_rec.reg_minimum_req_by_date,
inventory_item_id = l_evo_rec.INVENTORY_ITEM_ID,
organization_id = l_evo_rec.ORGANIZATION_ID,
pricelist_header_id = l_evo_rec.pricelist_header_id,
pricelist_line_id = l_evo_rec.pricelist_line_id,
waitlist_action_type_code = l_evo_rec.waitlist_action_type_code,
stream_type_code = l_evo_rec.stream_type_code,
owner_user_id = l_evo_rec.owner_user_id,
event_full_flag = l_evo_rec.event_full_flag,
forecasted_revenue = l_evo_rec.forecasted_revenue,
actual_revenue = l_evo_rec.actual_revenue,
forecasted_cost = l_evo_rec.forecasted_cost,
actual_cost = l_evo_rec.actual_cost,
fund_source_type_code = l_evo_rec.fund_source_type_code,
fund_source_id = l_evo_rec.fund_source_id,
cert_credit_type_code = l_evo_rec.cert_credit_type_code,
certification_credits = l_evo_rec.certification_credits,
coordinator_id = l_evo_rec.coordinator_id,
priority_type_code = l_evo_rec.priority_type_code,
cancellation_reason_code = l_evo_rec.cancellation_reason_code,
email = l_evo_rec.email,
phone = l_evo_rec.phone,
fund_amount_tc = l_evo_rec.fund_amount_tc,
fund_amount_fc = l_evo_rec.fund_amount_fc,
currency_code_tc = l_evo_rec.currency_code_tc,
currency_code_fc = l_evo_rec.currency_code_fc,
url = l_evo_rec.url,
timezone_id = l_evo_rec.timezone_id,
--event_venue_id = l_evo_rec.event_venue_id,
inbound_script_name = l_evo_rec.inbound_script_name,
auto_register_flag = NVL(l_evo_rec.auto_register_flag, nvl(FND_PROFILE.value('AMS_AUTO_REGISTER_FLAG'), 'Y')),
attribute_category = l_evo_rec.attribute_category,
attribute1 = l_evo_rec.attribute1,
attribute2 = l_evo_rec.attribute2,
attribute3 = l_evo_rec.attribute3,
attribute4 = l_evo_rec.attribute4,
attribute5 = l_evo_rec.attribute5,
attribute6 = l_evo_rec.attribute6,
attribute7 = l_evo_rec.attribute7,
attribute8 = l_evo_rec.attribute8,
attribute9 = l_evo_rec.attribute9,
attribute10 = l_evo_rec.attribute10,
attribute11 = l_evo_rec.attribute11,
attribute12 = l_evo_rec.attribute12,
attribute13 = l_evo_rec.attribute13,
attribute14 = l_evo_rec.attribute14,
attribute15 = l_evo_rec.attribute15,
country_code = l_evo_rec.country_code,
business_unit_id = l_evo_rec.business_unit_id,
event_calendar = l_evo_rec.event_calendar,
start_period_name = l_evo_rec.start_period_name,
end_period_name = l_evo_rec.end_period_name,
global_flag = nvl(l_evo_rec.global_flag, 'N'),
task_id = l_evo_rec.task_id, --Hornet
--program_id = l_evo_rec.program_id, --Hornet
parent_type = l_evo_rec.parent_type, --Hornet
parent_id = l_evo_rec.parent_id --Hornet
,CREATE_ATTENDANT_LEAD_FLAG = l_evo_rec.CREATE_ATTENDANT_LEAD_FLAG /*hornet*/
,CREATE_REGISTRANT_LEAD_FLAG = l_evo_rec.CREATE_REGISTRANT_LEAD_FLAG /*hornet*/
--,EVENT_OBJECT_TYPE = l_evo_rec.event_object_type /* hornet*/
,reg_timezone_id = l_evo_rec.reg_timezone_id
,event_password = l_evo_rec.event_password /* Hornet : added for imeeting integration*/
,record_event_flag = l_evo_rec.record_event_flag /* Hornet : added for imeeting integration*/
,allow_register_in_middle_flag = l_evo_rec.allow_register_in_middle_flag /* Hornet : added for imeeting integration*/
,publish_attendees_flag = l_evo_rec.publish_attendees_flag /* Hornet : added for imeeting integration*/
,direct_join_flag = l_evo_rec.direct_join_flag /* Hornet : added for imeeting integration*/
,event_notification_method = l_evo_rec.event_notification_method /* Hornet : added for imeeting integration*/
,actual_start_time = l_evo_rec.actual_start_time /* Hornet : added for imeeting integration*/
,actual_end_time = l_evo_rec.actual_end_time /* Hornet : added for imeeting integration*/
,server_id = l_evo_rec.server_id /* Hornet : added for imeeting integration*/
,OWNER_FND_USER_ID = l_evo_rec.OWNER_FND_USER_ID /* Hornet : added for imeeting integration*/
,MEETING_DIAL_IN_INFO = l_evo_rec.MEETING_DIAL_IN_INFO /* Hornet : added for imeeting integration*/
,MEETING_EMAIL_SUBJECT = l_evo_rec.MEETING_EMAIL_SUBJECT /* Hornet : added for imeeting integration*/
,MEETING_SCHEDULE_TYPE = l_evo_rec.MEETING_SCHEDULE_TYPE /* Hornet : added for imeeting integration*/
,MEETING_STATUS = l_evo_rec.MEETING_STATUS /* Hornet : added for imeeting integration*/
,PUBLISH_FLAG = l_evo_rec.PUBLISH_FLAG /* Hornet : added for imeeting integration*/
,MEETING_ENCRYPTION_KEY_CODE = l_evo_rec.MEETING_ENCRYPTION_KEY_CODE /* Hornet : added for imeeting integration*/
,MEETING_MISC_INFO = l_evo_rec.MEETING_MISC_INFO /* Hornet : added for imeeting integration*/
,NUMBER_OF_ATTENDEES = l_evo_rec.NUMBER_OF_ATTENDEES /* Hornet : added for imeeting integration*/
,EVENT_PURPOSE_CODE = l_evo_rec.EVENT_PURPOSE_CODE /* Hornet */
WHERE event_offer_id = l_evo_rec.event_offer_id
AND object_version_number = l_evo_rec.object_version_number;
UPDATE ams_event_offers_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_evo_rec.object_version_number + 1,
private_flag = l_evo_rec.private_flag,
active_flag = l_evo_rec.active_flag,
source_code = l_evo_rec.source_code,
event_level = l_evo_rec.event_level,
user_status_id = l_evo_rec.user_status_id,
system_status_code = l_evo_rec.system_status_code,
last_status_date = l_evo_rec.last_status_date,
event_type_code = l_evo_rec.event_type_code,
event_delivery_method_id = l_dlv_id,
event_language_code = l_evo_rec.event_language_code,
event_location_id = l_evo_rec.event_location_id,
overflow_flag = l_evo_rec.overflow_flag,
partner_flag = l_evo_rec.partner_flag,
event_standalone_flag = l_evo_rec.event_standalone_flag,
reg_frozen_flag = l_evo_rec.reg_frozen_flag,
reg_required_flag = l_evo_rec.reg_required_flag,
reg_charge_flag = l_evo_rec.reg_charge_flag,
reg_invited_only_flag = l_evo_rec.reg_invited_only_flag,
reg_waitlist_allowed_flag = l_evo_rec.reg_waitlist_allowed_flag,
reg_overbook_allowed_flag = l_evo_rec.reg_overbook_allowed_flag,
parent_event_offer_id = l_evo_rec.parent_event_offer_id,
event_duration = l_evo_rec.event_duration,
event_duration_uom_code = l_evo_rec.event_duration_uom_code,
--event_start_date = l_evo_rec.event_start_date,
event_start_date_time = l_evo_rec.event_start_date_time,
--event_end_date = l_evo_rec.event_end_date,
event_end_date_time = l_evo_rec.event_end_date_time,
reg_start_date = l_evo_rec.reg_start_date,
reg_start_time = to_date(to_char(l_evo_rec.reg_start_time,'HH24:MI'),'HH24:MI'),
reg_end_date = l_evo_rec.reg_end_date,
reg_end_time = to_date(to_char(l_evo_rec.reg_end_time,'HH24:MI'),'HH24:MI'),
reg_maximum_capacity = l_evo_rec.reg_maximum_capacity,
reg_overbook_pct = l_evo_rec.reg_overbook_pct,
reg_effective_capacity = l_evo_rec.reg_effective_capacity,
reg_waitlist_pct = l_evo_rec.reg_waitlist_pct,
reg_minimum_capacity = l_evo_rec.reg_minimum_capacity,
reg_minimum_req_by_date = l_evo_rec.reg_minimum_req_by_date,
inventory_item_id = l_evo_rec.INVENTORY_ITEM_ID,
organization_id = l_evo_rec.ORGANIZATION_ID,
pricelist_header_id = l_evo_rec.pricelist_header_id,
pricelist_line_id = l_evo_rec.pricelist_line_id,
waitlist_action_type_code = l_evo_rec.waitlist_action_type_code,
stream_type_code = l_evo_rec.stream_type_code,
owner_user_id = l_evo_rec.owner_user_id,
event_full_flag = l_evo_rec.event_full_flag,
forecasted_revenue = l_evo_rec.forecasted_revenue,
actual_revenue = l_evo_rec.actual_revenue,
forecasted_cost = l_evo_rec.forecasted_cost,
actual_cost = l_evo_rec.actual_cost,
fund_source_type_code = l_evo_rec.fund_source_type_code,
fund_source_id = l_evo_rec.fund_source_id,
cert_credit_type_code = l_evo_rec.cert_credit_type_code,
certification_credits = l_evo_rec.certification_credits,
coordinator_id = l_evo_rec.coordinator_id,
priority_type_code = l_evo_rec.priority_type_code,
cancellation_reason_code = l_evo_rec.cancellation_reason_code,
email = l_evo_rec.email,
phone = l_evo_rec.phone,
fund_amount_tc = l_evo_rec.fund_amount_tc,
fund_amount_fc = l_evo_rec.fund_amount_fc,
currency_code_tc = l_evo_rec.currency_code_tc,
currency_code_fc = l_evo_rec.currency_code_fc,
url = l_evo_rec.url,
timezone_id = l_evo_rec.timezone_id,
-- event_venue_id = l_evo_rec.event_venue_id,
inbound_script_name = l_evo_rec.inbound_script_name,
auto_register_flag = NVL(l_evo_rec.auto_register_flag, nvl(FND_PROFILE.value('AMS_AUTO_REGISTER_FLAG'), 'Y')),
attribute_category = l_evo_rec.attribute_category,
attribute1 = l_evo_rec.attribute1,
attribute2 = l_evo_rec.attribute2,
attribute3 = l_evo_rec.attribute3,
attribute4 = l_evo_rec.attribute4,
attribute5 = l_evo_rec.attribute5,
attribute6 = l_evo_rec.attribute6,
attribute7 = l_evo_rec.attribute7,
attribute8 = l_evo_rec.attribute8,
attribute9 = l_evo_rec.attribute9,
attribute10 = l_evo_rec.attribute10,
attribute11 = l_evo_rec.attribute11,
attribute12 = l_evo_rec.attribute12,
attribute13 = l_evo_rec.attribute13,
attribute14 = l_evo_rec.attribute14,
attribute15 = l_evo_rec.attribute15,
country_code = l_evo_rec.country_code,
business_unit_id = l_evo_rec.business_unit_id,
event_calendar = l_evo_rec.event_calendar,
start_period_name = l_evo_rec.start_period_name,
end_period_name = l_evo_rec.end_period_name,
global_flag = nvl(l_evo_rec.global_flag, 'N')
,reg_timezone_id = l_evo_rec.reg_timezone_id
,event_password = l_evo_rec.event_password /* Hornet : added for imeeting integration*/
,record_event_flag = l_evo_rec.record_event_flag /* Hornet : added for imeeting integration*/
,allow_register_in_middle_flag = l_evo_rec.allow_register_in_middle_flag /* Hornet : added for imeeting integration*/
,publish_attendees_flag = l_evo_rec.publish_attendees_flag /* Hornet : added for imeeting integration*/
,direct_join_flag = l_evo_rec.direct_join_flag /* Hornet : added for imeeting integration*/
,event_notification_method = l_evo_rec.event_notification_method /* Hornet : added for imeeting integration*/
,actual_start_time = l_evo_rec.actual_start_time /* Hornet : added for imeeting integration*/
,actual_end_time = l_evo_rec.actual_end_time /* Hornet : added for imeeting integration*/
,server_id = l_evo_rec.server_id /* Hornet : added for imeeting integration*/
,OWNER_FND_USER_ID = l_evo_rec.OWNER_FND_USER_ID /* Hornet : added for imeeting integration*/
,MEETING_DIAL_IN_INFO = l_evo_rec.MEETING_DIAL_IN_INFO /* Hornet : added for imeeting integration*/
,MEETING_EMAIL_SUBJECT = l_evo_rec.MEETING_EMAIL_SUBJECT /* Hornet : added for imeeting integration*/
,MEETING_SCHEDULE_TYPE = l_evo_rec.MEETING_SCHEDULE_TYPE /* Hornet : added for imeeting integration*/
,MEETING_STATUS = l_evo_rec.MEETING_STATUS /* Hornet : added for imeeting integration*/
,PUBLISH_FLAG = l_evo_rec.PUBLISH_FLAG /* Hornet : added for imeeting integration*/
,MEETING_ENCRYPTION_KEY_CODE = l_evo_rec.MEETING_ENCRYPTION_KEY_CODE /* Hornet : added for imeeting integration*/
,MEETING_MISC_INFO = l_evo_rec.MEETING_MISC_INFO /* Hornet : added for imeeting integration*/
,NUMBER_OF_ATTENDEES = l_evo_rec.NUMBER_OF_ATTENDEES /* Hornet : added for imeeting integration*/
,EVENT_PURPOSE_CODE = l_evo_rec.EVENT_PURPOSE_CODE /* Hornet */
WHERE event_offer_id = l_evo_rec.event_offer_id
AND object_version_number = l_evo_rec.object_version_number;
update ams_event_offers_all_tl set
event_offer_name = l_evo_rec.event_offer_name,
event_mktg_message = l_evo_rec.event_mktg_message,
description = l_evo_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 event_offer_id = l_evo_rec.event_offer_id
AND USERENV('LANG') IN (language, source_lang);
UPDATE ams_event_offers_all_b SET
event_start_date = l_evo_rec.event_start_date,
event_end_date = l_evo_rec.event_end_date,
event_venue_id = l_evo_rec.event_venue_id
WHERE event_offer_id = l_evo_rec.event_offer_id;
AMS_EvhRules_PVT.update_event_status(
p_event_id => l_evo_rec.event_offer_id,
p_event_activity_type => l_evo_rec.event_object_type, --'EVEO',
p_user_status_id => l_evo_rec.user_status_id,
p_fund_amount_tc => l_evo_rec.fund_amount_tc,
p_currency_code_tc => l_evo_rec.currency_code_tc
);
AMS_EvhRules_PVT.Add_Update_Access_record(p_object_type => l_evo_rec.event_object_type, --'EVEO',
p_object_id => l_evo_rec.event_offer_id,
p_Owner_user_id => l_evo_rec.owner_user_id,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
UPDATE ams_act_resources
SET system_status_code = 'CANCELLED',
object_version_number = l_obj_num+1,
user_status_id = (SELECT user_status_id
FROM ams_user_statuses_b
WHERE system_status_type = 'AMS_EVENT_AGENDA_STATUS'
AND system_status_code = 'CANCELLED'
-- added by soagrawa on 25-feb-2003 for bug# 2820297
AND DEFAULT_FLAG = 'Y')
WHERE activity_resource_id = l_resource_id;
UPDATE ams_act_resources
SET system_status_code = 'CANCELLED',
object_version_number = l_obj_num+1,
user_status_id = (SELECT user_status_id
FROM ams_user_statuses_b
WHERE system_status_type = 'AMS_EVENT_AGENDA_STATUS'
AND system_status_code = 'CANCELLED'
-- added by soagrawa on 25-feb-2003 for bug# 2820297
AND DEFAULT_FLAG = 'Y')
WHERE activity_resource_id IN (SELECT activity_resource_id
FROM ams_act_resources
WHERE act_resource_used_by_id IN ( SELECT agenda_id
FROM ams_agendas_b
WHERE parent_id IN (SELECT agenda_id
FROM ams_agendas_b
WHERE parent_id = l_evo_rec.event_offer_id)));
ROLLBACK TO update_event_offer;
ROLLBACK TO update_event_offer;
ROLLBACK TO update_event_offer;
END update_event_offer;
PROCEDURE check_evo_update_ok_items(
p_evo_rec IN evo_rec_type,
x_return_status OUT NOCOPY VARCHAR2
)
IS
BEGIN
x_return_status := FND_API.g_ret_sts_success;
END check_evo_update_ok_items;
SELECT 1 FROM DUAL WHERE EXISTS (select 1 from ams_source_codes
where SOURCE_CODE = src_code_in);
in evo_rec to update/create any more.. we have inv_num, which is always validated for
uniqueness in call to create_inv_item procedure call
----------------------- inventory_item_id ------------------------
-- gdeodhar: There was a WHERE in the following clause. Took it out
-- This was Ravi's instruction.
-- looks like AMS_Utility_PVT takes care of adding WHERE and AND accordingly.
l_where_clause := ' organization_id = ' || p_evo_rec.organization_id;
IF p_validation_mode = JTF_PLSQL_API.g_update THEN
check_evo_update_ok_items(
p_evo_rec => p_evo_rec,
x_return_status => x_return_status
);
select ACTUAL_EXEC_START_DATE, ACTUAL_EXEC_END_DATE
from AMS_CAMPAIGNS_ALL_B
where CAMPAIGN_ID = id_in
and ROLLUP_TYPE = 'RCAM';
ELSE -- coming from update
l_evo_start_date_time := p_complete_rec.event_start_date_time;
PROCEDURE check_evo_update(
p_evo_rec IN OUT NOCOPY AMS_EVENTOFFER_PVT.evo_rec_type,
x_return_status OUT NOCOPY VARCHAR2
)
IS
CURSOR c_evo IS
SELECT *
FROM ams_event_offers_vl
WHERE event_offer_id = p_evo_rec.event_offer_id;
SELECT 1
FROM ams_source_codes
WHERE source_code = p_evo_rec.source_code
AND active_flag = 'Y';
SELECT agenda_id
FROM ams_agendas_b
WHERE parent_id = id_in;
SELECT agenda_id
FROM ams_agendas_b
WHERE parent_id = id_in;
AMS_Utility_PVT.debug_message(p_evo_rec.event_offer_id ||': check evo_update');
update ams_agendas_b
set room_id = null
,object_version_number = object_version_number + 1
where agenda_id = l_session_id;
AMS_Utility_PVT.error_message('AMS_EVO_UPDATE_OFFER_NAME');
AMS_Utility_PVT.error_message('AMS_EVO_UPDATE_EVENT_TYPE');
AMS_Utility_PVT.error_message('AMS_EVO_UPDATE_START_DATE');
AMS_Utility_PVT.error_message('AMS_EVO_UPDATE_END_DATE');
END check_evo_update;
select source_code from ams_event_offers_all_b
where event_offer_id = id_in;
/* IF p_validation_mode = JTF_PLSQL_API.g_update
THEN
IF p_evo_rec.event_venue_id <> p_complete_rec.event_venue_id
IF p_complete_rec.event_venue_id is NULL
THEN
OPEN c_get_track_id(p_evo_rec.event_offer_id);
update ams_agendas_b
set room_id = null
,object_version_number = object_version_number + 1
where agenda_id = l_session_id;
x_evo_rec.last_update_date := FND_API.g_miss_date;
x_evo_rec.last_updated_by := FND_API.g_miss_num;
x_evo_rec.last_update_login := FND_API.g_miss_num;
x_evo_rec.LAST_UPDATE_DATE := FND_API.g_miss_date;
x_evo_rec.LAST_UPDATED_BY := FND_API.g_miss_num;
x_evo_rec.LAST_UPDATE_LOGIN := FND_API.g_miss_num;
SELECT *
FROM ams_event_offers_vl
WHERE event_offer_id = p_evo_rec.event_offer_id;
SELECT city, state, country
FROM hz_locations
WHERE location_id = loc_id;
SELECT *
FROM ams_event_headers_vl
WHERE event_header_id = p_src_evh_id;
SELECT 'x'
FROM ams_custom_setup_attr
WHERE custom_setup_id = nvl(id_in, 1006)
and OBJECT_ATTRIBUTE = 'RESP';
SELECT event_header_id
FROM ams_event_headers_vl
WHERE parent_event_header_id = p_src_evh_id;
SELECT attribute_defined_flag
FROM ams_object_attributes
WHERE object_type = 'EVEH'
AND object_id = p_src_evh_id
AND object_attribute = 'RESP';
SELECT object_attribute
FROM ams_custom_setup_attr
WHERE custom_setup_id = l_setup_id;
AMS_CPYutility_pvt.insert_log_mesg(x_transaction_id);
PROCEDURE Unit_Test_Insert
IS
-- local variables
l_evo_rec AMS_EVENTOFFER_PVT.evo_rec_type;
END Unit_Test_Insert;
SELECT distinct(pricelist_header_id)
FROM ams_event_offers_all_b evo, qp_price_lists_v qph
WHERE evo.pricelist_header_id = qph.price_list_id
AND qph.currency_code = curr_code;
SELECT actual_exec_start_date , actual_exec_end_date
FROM ams_campaigns_all_b
WHERE campaign_id = p_evo_rec.parent_id;
SELECT active_from_date, active_to_date
FROM ams_event_headers_all_b
WHERE event_header_id = p_evo_rec.event_header_id;
SELECT event_start_date, event_end_date
FROM ams_event_offers_all_b
WHERE event_offer_id = p_evo_rec.parent_event_offer_id and system_status_code<> 'CANCELLED';--implemented ER2381975 by anchaudh.
SELECT status_code
FROM ams_campaigns_all_b
WHERE campaign_id = p_evo_rec.parent_id;
SELECT system_status_code
FROM ams_event_headers_all_b
WHERE event_header_id = p_evo_rec.event_header_id;
PROCEDURE Update_Metrics (
p_evo_rec IN evo_rec_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_program_id NUMBER;
SELECT parent_id from ams_event_offers_v
WHERE event_offer_id = p_evo_rec.event_offer_id;
1) When Program is removed or updated (changed)
2) No need of calling when program is attached first time
*/
IF( l_program_id IS NOT NULL )THEN
IF( l_program_id <> nvl(p_evo_rec.parent_id,0))THEN
AMS_ACTMETRIC_PVT.INVALIDATE_ROLLUP(
p_api_version => l_api_version,
p_init_msg_list => Fnd_Api.g_false,
p_commit => Fnd_Api.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_used_by_type => 'EONE',
p_used_by_id => p_evo_rec.event_offer_id
);
END Update_Metrics;
SELECT system_status_code
FROM ams_user_statuses_b
WHERE user_status_id = p_evo_rec.user_status_id
AND system_status_type = 'AMS_EVENT_STATUS';
SELECT event_venue_id, event_start_date, event_end_date, system_status_code
FROM ams_event_offers_all_b
WHERE event_offer_id = p_evo_rec.event_offer_id;
l_bind_values.DELETE;
l_bind_names.DELETE;
l_bind_values.DELETE;
l_bind_names.DELETE;
l_bind_values.DELETE;
l_bind_names.DELETE;
l_bind_values.DELETE;
l_bind_names.DELETE;
l_bind_values.DELETE;
l_bind_names.DELETE;