[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT user_id
FROM ams_jtf_rs_emp_v
WHERE resource_id = p_resource_id;
PROCEDURE Insert_evtregs( p_evt_regs_Rec IN evt_regs_Rec_Type
, p_system_status_code IN VARCHAR2
, p_block_fulfillment IN VARCHAR2 := FND_API.G_FALSE
, x_confirmation_code OUT NOCOPY VARCHAR2
, x_event_registration_id OUT NOCOPY NUMBER
)
IS
l_evt_regs_Rec evt_regs_Rec_Type := p_evt_regs_Rec;
select email_address
from hz_parties
where party_id = p_party_id;
select email_address
from hz_parties hzp,
hz_relationships hzpr
where hzp.party_id = hzpr.party_id
and hzpr.object_id = p_party_id
and hzpr.subject_id = p_contact_id;
SELECT event_object_type,
trunc(event_end_date) + 1,
parent_type
from ams_event_offers_all_b
where event_offer_id = p_event_offer_id;
SELECT ams_event_registrations_s.NEXTVAL
FROM DUAL;
SELECT 1
FROM dual
WHERE EXISTS (SELECT 1
-- FROM AMS_event_offers_all_B Modified by ptendulk on 12-Feb-2002
-- WHERE event_offer_id = l_id);
SELECT ams_event_reg_confirmation_s.nextval
FROM dual;
SELECT ams_reg_waitlist_priority_s.nextval
FROM dual;
SELECT user_status_id
FROM ams_user_statuses_b
WHERE SYSTEM_STATUS_CODE = p_system_status_code
and SYSTEM_STATUS_TYPE = 'AMS_EVENT_REG_STATUS'
and default_flag = 'Y'; --anchaudh:fixed bug#3020564 on 24-jun-2003.
SELECT schedule_id
FROM ams_campaign_schedules_b
WHERE related_event_id = obj_id;
INSERT INTO AMS_EVENT_REGISTRATIONS(
EVENT_REGISTRATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
EVENT_OFFER_ID,
APPLICATION_ID,
ACTIVE_FLAG,
OWNER_USER_ID,
DATE_REGISTRATION_PLACED,
USER_STATUS_ID,
SYSTEM_STATUS_CODE,
LAST_REG_STATUS_DATE,
REG_SOURCE_TYPE_CODE,
REGISTRATION_SOURCE_ID,
CONFIRMATION_CODE,
SOURCE_CODE,
REGISTRATION_GROUP_ID,
REGISTRANT_PARTY_ID,
REGISTRANT_CONTACT_ID,
REGISTRANT_ACCOUNT_ID,
ATTENDANT_PARTY_ID,
ATTENDANT_CONTACT_ID,
ATTENDANT_ACCOUNT_ID,
ORIGINAL_REGISTRANT_CONTACT_ID,
PROSPECT_FLAG,
ATTENDED_FLAG,
CONFIRMED_FLAG,
EVALUATED_FLAG,
ATTENDANCE_RESULT_CODE,
WAITLISTED_PRIORITY,
TARGET_LIST_ID,
INBOUND_MEDIA_ID,
INBOUND_CHANNEL_ID,
CANCELLATION_CODE,
CANCELLATION_REASON_CODE,
ATTENDANCE_FAILURE_REASON,
ATTENDANT_LANGUAGE,
SALESREP_ID,
ORDER_HEADER_ID,
ORDER_LINE_ID,
DESCRIPTION,
MAX_ATTENDEE_OVERRIDE_FLAG,
INVITE_ONLY_OVERRIDE_FLAG,
PAYMENT_STATUS_CODE,
AUTO_REGISTER_FLAG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
attendee_role_type, /* Hornet : added for imeeting integration*/
notification_type, /* Hornet : added for imeeting integration*/
last_notified_time, /* Hornet : added for imeeting integration*/
EVENT_JOIN_TIME,/* Hornet : added for imeeting integration*/
EVENT_EXIT_TIME, /* Hornet : added for imeeting integration*/
MEETING_ENCRYPTION_KEY_CODE /* Hornet : added for imeeting integration*/
) VALUES (
l_event_registration_id,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.conc_login_id,
1, -- object_version_number
l_evt_regs_rec.EVENT_OFFER_ID,
l_evt_regs_rec.APPLICATION_ID,
nvl(l_evt_regs_rec.ACTIVE_FLAG, 'Y'),
l_evt_regs_rec.OWNER_USER_ID,
nvl(l_date_reg_placed,sysdate),
l_evt_regs_rec.USER_STATUS_ID,
l_system_status_code,
nvl(l_evt_regs_rec.LAST_REG_STATUS_DATE, sysdate),
l_evt_regs_rec.REG_SOURCE_TYPE_CODE,
l_evt_regs_rec.REGISTRATION_SOURCE_ID,
l_confirmation_code,
l_evt_regs_rec.SOURCE_CODE,
l_evt_regs_rec.REGISTRATION_GROUP_ID,
l_evt_regs_rec.REGISTRANT_PARTY_ID,
l_evt_regs_rec.REGISTRANT_CONTACT_ID,
l_evt_regs_rec.REGISTRANT_ACCOUNT_ID,
l_evt_regs_rec.ATTENDANT_PARTY_ID,
l_evt_regs_rec.ATTENDANT_CONTACT_ID,
l_evt_regs_rec.ATTENDANT_ACCOUNT_ID,
nvl(l_evt_regs_rec.ORIGINAL_REGISTRANT_CONTACT_ID,l_evt_regs_rec.REGISTRANT_CONTACT_ID),
nvl(l_evt_regs_rec.PROSPECT_FLAG, 'N'),
nvl(l_evt_regs_rec.ATTENDED_FLAG, 'N'),
nvl(l_evt_regs_rec.CONFIRMED_FLAG, 'N'),
nvl(l_evt_regs_rec.EVALUATED_FLAG, 'N'),
l_evt_regs_rec.ATTENDANCE_RESULT_CODE,
l_waitlisted_priority,
l_evt_regs_rec.TARGET_LIST_ID,
l_evt_regs_rec.INBOUND_MEDIA_ID,
l_evt_regs_rec.INBOUND_CHANNEL_ID,
l_evt_regs_rec.CANCELLATION_CODE,
l_evt_regs_rec.CANCELLATION_REASON_CODE,
l_evt_regs_rec.ATTENDANCE_FAILURE_REASON,
l_evt_regs_rec.ATTENDANT_LANGUAGE,
l_evt_regs_rec.SALESREP_ID,
l_evt_regs_rec.ORDER_HEADER_ID,
l_evt_regs_rec.ORDER_LINE_ID,
l_evt_regs_rec.DESCRIPTION,
nvl(l_evt_regs_rec.MAX_ATTENDEE_OVERRIDE_FLAG, 'N'),
nvl(l_evt_regs_rec.INVITE_ONLY_OVERRIDE_FLAG, 'N'),
l_evt_regs_rec.PAYMENT_STATUS_CODE,
nvl(l_evt_regs_rec.AUTO_REGISTER_FLAG, 'Y'),
l_evt_regs_rec.ATTRIBUTE_CATEGORY,
l_evt_regs_rec.ATTRIBUTE1,
l_evt_regs_rec.ATTRIBUTE2,
l_evt_regs_rec.ATTRIBUTE3,
l_evt_regs_rec.ATTRIBUTE4,
l_evt_regs_rec.ATTRIBUTE5,
l_evt_regs_rec.ATTRIBUTE6,
l_evt_regs_rec.ATTRIBUTE7,
l_evt_regs_rec.ATTRIBUTE8,
l_evt_regs_rec.ATTRIBUTE9,
l_evt_regs_rec.ATTRIBUTE10,
l_evt_regs_rec.ATTRIBUTE11,
l_evt_regs_rec.ATTRIBUTE12,
l_evt_regs_rec.ATTRIBUTE13,
l_evt_regs_rec.ATTRIBUTE14,
l_evt_regs_rec.ATTRIBUTE15,
l_evt_regs_rec.attendee_role_type, /* Hornet : added for imeeting integration*/
l_evt_regs_rec.notification_type, /* Hornet : added for imeeting integration*/
l_evt_regs_rec.last_notified_time, /* Hornet : added for imeeting integration*/
l_evt_regs_rec.EVENT_JOIN_TIME, /* Hornet : added for imeeting integration*/
l_evt_regs_rec.EVENT_EXIT_TIME, /* Hornet : added for imeeting integration*/
l_evt_regs_rec.MEETING_ENCRYPTION_KEY_CODE /* Hornet : added for imeeting integration*/
);
END Insert_evtregs;
select count(*)
from AMS_EVENT_REGISTRATIONS
where event_offer_id = p_event_offer_id
and system_status_code = 'REGISTERED';
select count(*)
from AMS_EVENT_REGISTRATIONS
where event_offer_id = p_event_offer_id
and system_status_code = 'WAITLISTED';
select reg_maximum_capacity into l_max_capacity --anchaudh added for bug#3119915.
from AMS_event_offers_all_B
where event_offer_id = p_event_offer_id;
select count(*)
from AMS_EVENT_REGISTRATIONS
where event_offer_id = l_event_offer_id
and system_status_code = 'WAITLISTED';
SELECT 1
FROM ams_list_entries le
, ams_act_lists al
WHERE al.list_used_by_id = p_event_offer_id
AND al.list_used_by in ('EVEO', 'EONE')
AND al.list_act_type = 'TARGET'
AND le.list_header_id = al.list_header_id
AND le.party_id = p_attendant_contact_id;
SELECT COUNT(1)
FROM ams_list_entries le
WHERE EXISTS (SELECT 1
FROM ams_act_lists al
WHERE list_used_by_id = p_event_offer_id
AND list_used_by in ('EVEO', 'EONE')
AND list_act_type = 'TARGET'
AND le.list_header_id = al.list_header_id
AND le.party_id = p_attendant_contact_id );
SELECT COUNT(1)
FROM ams_list_entries le
WHERE EXISTS (SELECT 1
FROM ams_act_lists al
WHERE list_used_by_id = p_event_offer_id
AND list_used_by in ('EVEO', 'EONE')
AND list_act_type = 'TARGET'
AND le.list_header_id = al.list_header_id
AND ((le.party_id = p_attendant_contact_id
AND le.list_entry_source_system_type = 'PERSON_LIST')
-- dbiswas modified the following to carry fix for NI bug# 2610067 on 16-apr-2003
or(le.party_id IN (SELECT subject_id -- person id
FROM ar.hz_relationships
WHERE party_id = p_attendant_contact_id
-- soagrawa 23-may-2003 fixed bug# 2949603
-- AND directional_flag = 'F'
AND subject_Type = 'PERSON'
AND object_Type = 'ORGANIZATION'
)
and le.list_entry_source_system_type ='ORGANIZATION_CONTACT_LIST')));
(SELECT party_id
FROM ar.hz_relationships
WHERE subject_id = p_attendant_contact_id
AND subject_type = 'PERSON'
AND object_type = 'ORGANIZATION'
AND object_id = le.col147
AND object_id = p_attendant_party_id)
and le.list_entry_source_system_type ='ORGANIZATION_CONTACT_LIST')));
/* select count(1)
from ams_list_entries le
where exists (select 1
from ams_act_lists al
where list_used_by_id = p_event_offer_id
and list_used_by in ('EVEO', 'EONE')
and list_act_type = 'TARGET'
and le.list_header_id = al.list_header_id
and le.party_id = p_attendant_contact_id
);
and exists (select 1
from hz_relationships re
where subject_id = p_attendant_contact_id
and subject_type = 'PERSON'
and le.party_id = re.party_id
);
PROCEDURE transfer_insert( 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_old_offer_id IN NUMBER
, p_new_offer_id IN NUMBER
, p_system_status_code IN VARCHAR2
, p_reg_status_date IN DATE
, p_old_confirmation_code IN VARCHAR2
, p_registrant_account_id IN NUMBER
, p_registrant_party_id IN NUMBER
, p_registrant_contact_id IN NUMBER
, p_attendant_party_id IN NUMBER
, p_attendant_contact_id IN NUMBER
, x_new_confirmation_code OUT NOCOPY VARCHAR2
, x_new_system_status_code OUT NOCOPY VARCHAR2
, x_new_registration_id OUT NOCOPY NUMBER
, x_Return_Status OUT NOCOPY VARCHAR2
, x_Msg_Count OUT NOCOPY NUMBER
, x_Msg_Data OUT NOCOPY VARCHAR2
)
IS
l_return_status VARCHAR2(1);
l_api_name CONSTANT VARCHAR2(30) := 'transfer_insert';
SELECT
EVENT_REGISTRATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
EVENT_OFFER_ID,
APPLICATION_ID,
ACTIVE_FLAG,
OWNER_USER_ID ,
SYSTEM_STATUS_CODE,
DATE_REGISTRATION_PLACED,
USER_STATUS_ID,
LAST_REG_STATUS_DATE,
REG_SOURCE_TYPE_CODE,
REGISTRATION_SOURCE_ID,
CONFIRMATION_CODE,
SOURCE_CODE,
REGISTRATION_GROUP_ID,
REGISTRANT_PARTY_ID,
REGISTRANT_CONTACT_ID,
REGISTRANT_ACCOUNT_ID,
ATTENDANT_PARTY_ID,
ATTENDANT_CONTACT_ID,
ATTENDANT_ACCOUNT_ID,
ORIGINAL_REGISTRANT_CONTACT_ID,
PROSPECT_FLAG,
ATTENDED_FLAG,
CONFIRMED_FLAG,
EVALUATED_FLAG,
null,
ATTENDANCE_RESULT_CODE,
WAITLISTED_PRIORITY,
TARGET_LIST_ID,
INBOUND_MEDIA_ID,
INBOUND_CHANNEL_ID,
CANCELLATION_CODE,
CANCELLATION_REASON_CODE,
ATTENDANCE_FAILURE_REASON,
ATTENDANT_LANGUAGE,
SALESREP_ID,
ORDER_HEADER_ID,
ORDER_LINE_ID,
DESCRIPTION,
MAX_ATTENDEE_OVERRIDE_FLAG,
INVITE_ONLY_OVERRIDE_FLAG,
PAYMENT_STATUS_CODE,
AUTO_REGISTER_FLAG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
attendee_role_type, -- Hornet : added for imeeting integration
notification_type, -- Hornet : added for imeeting integration
last_notified_time, -- Hornet : added for imeeting integration
EVENT_JOIN_TIME, -- Hornet : added for imeeting integration
EVENT_EXIT_TIME, -- Hornet : added for imeeting integration
MEETING_ENCRYPTION_KEY_CODE -- Hornet : added for imeeting integration
FROM ams_event_registrations
WHERE confirmation_code = p_old_confirmation_code
and event_offer_id = p_old_offer_id;
SAVEPOINT transfer_insert_PVT;
Insert_evtRegs( l_evt_regs_rec
, p_system_status_code
, FND_API.G_FALSE
, x_new_confirmation_code
, x_new_registration_id
);
ROLLBACK TO transfer_insert_PVT;
ROLLBACK TO transfer_insert_PVT;
ROLLBACK TO transfer_insert_PVT;
END transfer_insert;
select
REG_INVITED_ONLY_FLAG,
REG_WAITLIST_ALLOWED_FLAG,
REG_REQUIRED_FLAG,
REG_FROZEN_FLAG,
REG_EFFECTIVE_CAPACITY,
REG_WAITLIST_PCT
from ams_event_offers_all_b
where EVENT_OFFER_ID = l_event_offer_id;
AMS_Utility_PVT.debug_message(l_full_name || ': insert');
Insert_evtRegs( p_evt_regs_rec => l_evt_regs_rec
, p_system_status_code => l_system_status_code
, p_block_fulfillment => p_block_fulfillment
, x_confirmation_code => x_confirmation_code
, x_event_registration_id => x_event_registration_id
);
Insert_evtRegs( p_evt_regs_rec => l_evt_regs_rec
, p_system_status_code => l_system_status_code
, p_block_fulfillment => p_block_fulfillment
, x_confirmation_code => x_confirmation_code
, x_event_registration_id => x_event_registration_id
);
Insert_evtRegs( p_evt_regs_rec => l_evt_regs_rec
, p_system_status_code => l_system_status_code
, p_block_fulfillment => p_block_fulfillment
, x_confirmation_code => x_confirmation_code
, x_event_registration_id => x_event_registration_id
);
PROCEDURE UPDATE_evtregs_wrapper( 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
, P_evt_regs_Rec IN evt_regs_Rec_Type
, p_block_fulfillment IN VARCHAR2 := FND_API.G_FALSE
, p_cancellation_reason_code IN VARCHAR2 := NULL
, x_cancellation_code OUT NOCOPY VARCHAR2
, X_Return_Status OUT NOCOPY VARCHAR2
, X_Msg_Count OUT NOCOPY NUMBER
, X_Msg_Data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_evtregs_wrapper';
SELECT *
FROM ams_event_registrations
WHERE event_registration_id = p_evt_regs_rec.event_registration_id;
select user_status_id
from ams_user_statuses_vl
where system_status_type = 'AMS_EVENT_REG_STATUS'
and system_status_code = 'CANCELLED'
and default_flag = 'Y';
SAVEPOINT UPDATE_evtregs_wrapper;
update_evtregs( p_api_version_number => p_api_version_number
, p_init_msg_list => p_init_msg_list
, p_commit => p_commit
, p_validation_level => p_validation_level
, p_evt_regs_rec => p_evt_regs_rec
, p_block_fulfillment => p_block_fulfillment
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
ROLLBACK TO UPDATE_evtregs_wrapper;
ROLLBACK TO UPDATE_evtregs_wrapper;
ROLLBACK TO UPDATE_evtregs_wrapper;
END Update_evtregs_wrapper;
PROCEDURE Update_evtregs( 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
, P_evt_regs_Rec IN evt_regs_Rec_Type
, p_block_fulfillment IN VARCHAR2 := FND_API.G_FALSE
, X_Return_Status OUT NOCOPY VARCHAR2
, X_Msg_Count OUT NOCOPY NUMBER
, X_Msg_Data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_evtregs';
SELECT event_object_type,
trunc(event_end_date) + 1,
parent_type
from ams_event_offers_all_b
where event_offer_id = p_event_offer_id;
select system_status_code
from ams_event_registrations
where event_registration_id = p_reg_id;
select e.reg_effective_capacity,
e.system_status_code,
u.name
from ams_event_offers_all_b e,
ams_user_statuses_vl u
where e.event_offer_id = p_event_id
and e.user_status_id = u.user_status_id;
select system_status_code
from ams_user_statuses_b
where user_status_id = p_status_id
and system_status_type = 'AMS_EVENT_REG_STATUS';
SELECT schedule_id
FROM ams_campaign_schedules_b
WHERE related_event_id = obj_id;
SAVEPOINT UPDATE_EvtRegs_PVT;
, p_validation_mode => JTF_PLSQL_API.g_update
, x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
AMS_Utility_PVT.Error_Message('AMS_EVENT_REG_UPDATE_ERROR', 'STATUS', l_event_status_name);
AMS_Utility_PVT.debug_message(l_full_name || ': update');
update AMS_EVENT_REGISTRATIONS set
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id,
OBJECT_VERSION_NUMBER = l_evt_regs_Rec.OBJECT_VERSION_NUMBER + 1,
EVENT_OFFER_ID = l_evt_regs_Rec.EVENT_OFFER_ID,
APPLICATION_ID = l_evt_regs_Rec.APPLICATION_ID,
ACTIVE_FLAG = nvl(l_evt_regs_Rec.ACTIVE_FLAG,'Y'),
OWNER_USER_ID = l_evt_regs_Rec.OWNER_USER_ID,
DATE_REGISTRATION_PLACED = nvl(l_evt_regs_Rec.DATE_REGISTRATION_PLACED,sysdate),
USER_STATUS_ID = l_evt_regs_Rec.USER_STATUS_ID,
SYSTEM_STATUS_CODE = l_evt_regs_Rec.SYSTEM_STATUS_CODE,
LAST_REG_STATUS_DATE = nvl(l_evt_regs_Rec.LAST_REG_STATUS_DATE, sysdate),
REG_SOURCE_TYPE_CODE = l_evt_regs_Rec.REG_SOURCE_TYPE_CODE,
REGISTRATION_SOURCE_ID = l_evt_regs_Rec.REGISTRATION_SOURCE_ID,
CONFIRMATION_CODE = l_evt_regs_Rec.CONFIRMATION_CODE,
SOURCE_CODE = l_evt_regs_Rec.SOURCE_CODE,
REGISTRATION_GROUP_ID = l_evt_regs_Rec.REGISTRATION_GROUP_ID,
REGISTRANT_PARTY_ID = l_evt_regs_Rec.REGISTRANT_PARTY_ID,
REGISTRANT_CONTACT_ID = l_evt_regs_Rec.REGISTRANT_CONTACT_ID,
ATTENDANT_PARTY_ID = l_evt_regs_Rec.ATTENDANT_PARTY_ID,
ATTENDANT_CONTACT_ID = l_evt_regs_Rec.ATTENDANT_CONTACT_ID,
ORIGINAL_REGISTRANT_CONTACT_ID = l_evt_regs_Rec.ORIGINAL_REGISTRANT_CONTACT_ID,
PROSPECT_FLAG = l_evt_regs_Rec.PROSPECT_FLAG,
ATTENDED_FLAG = l_evt_regs_Rec.ATTENDED_FLAG,
CONFIRMED_FLAG = l_evt_regs_Rec.CONFIRMED_FLAG,
EVALUATED_FLAG = l_evt_regs_Rec.EVALUATED_FLAG,
ATTENDANCE_RESULT_CODE = l_evt_regs_Rec.ATTENDANCE_RESULT_CODE,
WAITLISTED_PRIORITY = l_evt_regs_Rec.WAITLISTED_PRIORITY,
TARGET_LIST_ID = l_evt_regs_Rec.TARGET_LIST_ID,
INBOUND_MEDIA_ID = l_evt_regs_Rec.INBOUND_MEDIA_ID,
INBOUND_CHANNEL_ID = l_evt_regs_Rec.INBOUND_CHANNEL_ID,
CANCELLATION_CODE = l_evt_regs_Rec.CANCELLATION_CODE,
CANCELLATION_REASON_CODE = l_evt_regs_Rec.CANCELLATION_REASON_CODE,
ATTENDANCE_FAILURE_REASON = l_evt_regs_Rec.ATTENDANCE_FAILURE_REASON,
ATTENDANT_LANGUAGE = l_evt_regs_Rec.ATTENDANT_LANGUAGE,
SALESREP_ID = l_evt_regs_Rec.SALESREP_ID,
ORDER_HEADER_ID = l_evt_regs_Rec.ORDER_HEADER_ID,
ORDER_LINE_ID = l_evt_regs_Rec.ORDER_LINE_ID,
DESCRIPTION = l_evt_regs_Rec.DESCRIPTION,
MAX_ATTENDEE_OVERRIDE_FLAG = l_evt_regs_Rec.MAX_ATTENDEE_OVERRIDE_FLAG,
INVITE_ONLY_OVERRIDE_FLAG = l_evt_regs_Rec.INVITE_ONLY_OVERRIDE_FLAG,
PAYMENT_STATUS_CODE = l_evt_regs_Rec.PAYMENT_STATUS_CODE,
AUTO_REGISTER_FLAG = l_evt_regs_Rec.AUTO_REGISTER_FLAG,
ATTRIBUTE_CATEGORY = l_evt_regs_Rec.ATTRIBUTE_CATEGORY,
ATTRIBUTE1 = l_evt_regs_Rec.ATTRIBUTE1,
ATTRIBUTE2 = l_evt_regs_Rec.ATTRIBUTE2,
ATTRIBUTE3 = l_evt_regs_Rec.ATTRIBUTE3,
ATTRIBUTE4 = l_evt_regs_Rec.ATTRIBUTE4,
ATTRIBUTE5 = l_evt_regs_Rec.ATTRIBUTE5,
ATTRIBUTE6 = l_evt_regs_Rec.ATTRIBUTE6,
ATTRIBUTE7 = l_evt_regs_Rec.ATTRIBUTE7,
ATTRIBUTE8 = l_evt_regs_Rec.ATTRIBUTE8,
ATTRIBUTE9 = l_evt_regs_Rec.ATTRIBUTE9,
ATTRIBUTE10 = l_evt_regs_Rec.ATTRIBUTE10,
ATTRIBUTE11 = l_evt_regs_Rec.ATTRIBUTE11,
ATTRIBUTE12 = l_evt_regs_Rec.ATTRIBUTE12,
ATTRIBUTE13 = l_evt_regs_Rec.ATTRIBUTE13,
ATTRIBUTE14 = l_evt_regs_Rec.ATTRIBUTE14,
ATTRIBUTE15 = l_evt_regs_Rec.ATTRIBUTE15,
attendee_role_type = l_evt_regs_rec.attendee_role_type, -- Hornet : added for imeeting integration
notification_type = l_evt_regs_rec.notification_type, -- Hornet : added for imeeting integration
last_notified_time = l_evt_regs_rec.last_notified_time, -- Hornet : added for imeeting integration
EVENT_JOIN_TIME = l_evt_regs_rec.EVENT_JOIN_TIME, -- Hornet : added for imeeting integration
EVENT_EXIT_TIME = l_evt_regs_rec.EVENT_EXIT_TIME, -- Hornet : added for imeeting integration
MEETING_ENCRYPTION_KEY_CODE = l_evt_regs_rec.MEETING_ENCRYPTION_KEY_CODE -- Hornet : added for imeeting integration
where EVENT_REGISTRATION_ID = l_evt_regs_Rec.EVENT_REGISTRATION_ID
and object_version_number = l_evt_regs_Rec.object_version_number;
ROLLBACK TO UPDATE_EvtRegs_PVT;
ROLLBACK TO UPDATE_EvtRegs_PVT;
ROLLBACK TO UPDATE_EvtRegs_PVT;
End Update_evtregs;
SELECT event_object_type, parent_type
from ams_event_offers_all_b
where event_offer_id = p_event_offer_id;
event_registration_id , and write update cancel reg where clause
as in where clause of this cursor, instead of using event_registration_id
*/
-- indeed i need not get offer_id as confirmation code should be enuf to get all information..
l_csch_id NUMBER;
SELECT schedule_id
FROM ams_campaign_schedules_b
WHERE related_event_id = obj_id;
select
reg.event_registration_id,
-- soagrawa 03-feb-2003 bug# 2777302, now getting owner user id from event
-- reg.owner_user_id,
offers.owner_user_id,
offers.system_status_code,
usrsts.name,
reg.attended_flag,
offers.REG_CHARGE_FLAG,
offers.EVENT_END_DATE,
offers.EVENT_END_DATE_TIME,
offers.REG_END_DATE,
offers.REG_END_TIME
from ams_event_offers_all_b offers, ams_event_registrations reg, ams_user_statuses_vl usrsts
where offers.EVENT_OFFER_ID = l_event_offer_id
and reg.EVENT_OFFER_ID = l_event_offer_id
and reg.CONFIRMATION_CODE = l_confirmation_code
and nvl(l_registrant_party_id, reg.REGISTRANT_PARTY_ID) = reg.REGISTRANT_PARTY_ID
and offers.user_status_id = usrsts.user_status_id;
select
REGISTRANT_PARTY_ID,
CONFIRMATION_CODE
from ams_event_registrations
where EVENT_OFFER_ID = l_event_offer_id
and REGISTRATION_GROUP_ID = l_registration_group_id;
select ams_event_reg_cancellation_s.nextval
from dual;
select USER_STATUS_ID
from AMS_USER_STATUSES_VL
where SYSTEM_STATUS_CODE = 'CANCELLED'
and DEFAULT_FLAG = 'Y'
and SYSTEM_STATUS_TYPE = 'AMS_EVENT_REG_STATUS';
AMS_Utility_PVT.Error_Message('AMS_EVENT_REG_UPDATE_ERROR', 'STATUS', l_event_status_name);
UPDATE ams_event_registrations SET
system_status_code = 'CANCELLED',
USER_STATUS_ID = l_user_stat_id,
cancellation_code = l_cancellation_code,
cancellation_reason_code = l_cancellation_reason_code,
waitlisted_priority = null
-- added by dbiswas for NI issue on 19-mar-2003
, last_update_date = sysdate
, last_updated_by = FND_GLOBAL.USER_ID
, LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id
-- added by dbiswas to carry fix for NI issue on 16-apr-2003
, last_reg_status_date = sysdate
WHERE event_registration_id = l_event_registration_id
and object_version_number = p_object_version;
UPDATE ams_event_registrations SET
system_status_code = 'CANCELLED',
USER_STATUS_ID = l_user_stat_id,
cancellation_code = l_cancellation_code,
cancellation_reason_code = l_cancellation_reason_code,
waitlisted_priority = null
-- added by dbiswas for NI issue on 19-mar-2003
, last_update_date = sysdate
, last_updated_by = FND_GLOBAL.USER_ID
, LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id
-- added by dbiswas to carry fix for NI issue on 16-apr-2003
, last_reg_status_date = sysdate
WHERE event_registration_id = l_event_registration_id
and object_version_number = p_object_version;
-- sikalyan Fixed Bug 4185688 Updated the Conditional Check for a Registrant Group
IF ( (cancel_reg_conf_data.confirmation_code IS NOT NULL)
AND (cancel_reg_conf_data.registrant_party_id IS NOT NULL)
AND (cancel_reg_conf_data.confirmation_code = l_confirmation_code)
AND (cancel_reg_conf_data.registrant_party_id = l_registrant_party_id)
)
THEN
open cancel_get_offer_details( l_event_offer_id
, cancel_reg_conf_data.confirmation_code
, cancel_reg_conf_data.registrant_party_id
);
AMS_Utility_PVT.Error_Message('AMS_EVENT_REG_UPDATE_ERROR', 'STATUS', l_event_status_name);
UPDATE ams_event_registrations SET
system_status_code = 'CANCELLED',
USER_STATUS_ID = l_user_stat_id,
cancellation_code = l_cancellation_code,
cancellation_reason_code = l_cancellation_reason_code,
waitlisted_priority = null
-- added by dbiswas for NI issue on 19-mar-2003
, last_update_date = sysdate
, last_updated_by = FND_GLOBAL.USER_ID
, LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id
-- added by dbiswas to carry fix for NI issue on 16-apr-2003
, last_reg_status_date = sysdate
WHERE event_registration_id = l_event_registration_id
and object_version_number = p_object_version;
UPDATE ams_event_registrations SET
system_status_code = 'CANCELLED',
USER_STATUS_ID = l_user_stat_id,
cancellation_code = l_cancellation_code,
cancellation_reason_code = l_cancellation_reason_code,
waitlisted_priority = null
-- added by dbiswas for NI issue on 19-mar-2003
, last_update_date = sysdate
, last_updated_by = FND_GLOBAL.USER_ID
, LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id
-- added by dbiswas to carry fix for NI issue on 16-apr-2003
, last_reg_status_date = sysdate
WHERE event_registration_id = l_event_registration_id
and object_version_number = p_object_version;
SELECT event_registration_id
FROM ams_event_registrations
WHERE event_registration_id = l_reg_id
AND object_version_number = p_object_version
FOR UPDATE OF event_registration_id NOWAIT;
PROCEDURE delete_evtRegs( 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_object_version IN NUMBER
, p_event_registration_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_version_number CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'delete_evtRegs';
SAVEPOINT delete_evtRegs;
AMS_Utility_PVT.debug_message(l_full_name ||': delete');
UPDATE ams_event_registrations
SET active_flag = 'N'
WHERE event_registration_id = p_event_registration_id
AND object_version_number = p_object_version;
ROLLBACK TO delete_evtRegs;
ROLLBACK TO delete_evtRegs;
ROLLBACK TO delete_evtRegs;
END delete_evtRegs;
select
b.AUTO_REGISTER_FLAG,
b.REG_FROZEN_FLAG,
b.REG_EFFECTIVE_CAPACITY,
b.OWNER_USER_ID,
b.event_object_type,
t.EVENT_OFFER_NAME,
b.waitlist_action_type_code,
b.parent_type
from
ams_event_offers_all_b b,
AMS_EVENT_OFFERS_ALL_TL t
where b.event_offer_id = p_event_offer_id
and t.event_offer_id = p_event_offer_id;
select
event_registration_id,
--auto_register_flag,
SALESREP_ID,
ORDER_HEADER_ID,
ORDER_LINE_ID
from ams_event_registrations
where event_offer_id = p_event_offer_id
and system_status_code = 'WAITLISTED'
and waitlisted_priority = (select min(waitlisted_priority)
from ams_event_registrations
where event_offer_id = p_event_offer_id
and system_status_code = 'WAITLISTED'
);
SELECT schedule_id
FROM ams_campaign_schedules_b
WHERE related_event_id = obj_id;
update ams_event_registrations set
system_status_code = 'REGISTERED',
waitlisted_priority = null,
user_status_id = (select user_status_id
from ams_user_statuses_vl
where system_status_type = 'AMS_EVENT_REG_STATUS'
and system_status_code = 'REGISTERED'
and default_flag = 'Y'
)
where event_registration_id = l_min_wait_reg_id;
AMS_Utility_PVT.debug_message(l_full_name || ': update');
update AMS_EVENT_REGISTRATIONS set
ATTENDANT_PARTY_ID = p_attendant_party_id,
ATTENDANT_CONTACT_ID = p_attendant_contact_id,
ATTENDANT_ACCOUNT_ID = nvl(p_attendant_account_id,attendant_account_id),
REGISTRANT_PARTY_ID = nvl(p_registrant_party_id, registrant_party_id),
REGISTRANT_CONTACT_ID = p_registrant_contact_id,
REGISTRANT_ACCOUNT_ID = nvl(p_registrant_account_id, registrant_account_id),
ORIGINAL_REGISTRANT_CONTACT_ID = registrant_contact_id
where CONFIRMATION_CODE LIKE p_confirmation_code;
select
REG_WAITLIST_ALLOWED_FLAG,
REG_REQUIRED_FLAG,
REG_FROZEN_FLAG,
REG_EFFECTIVE_CAPACITY,
REG_WAITLIST_PCT
from ams_event_offers_all_b
where event_offer_id = l_event_offer_id;
select system_status_code
from ams_event_registrations
where confirmation_code = p_confirmation_code;
AMS_UTILITY_PVT.debug_message('Calling transfer_insert');
transfer_insert( p_Api_Version_Number => p_api_version_number
, p_Init_Msg_List => p_init_msg_list
, p_Commit => p_commit
, p_old_offer_id => p_old_offer_id
, p_new_offer_id => p_new_offer_id
, p_system_status_code => l_system_status_code
, p_reg_status_date => sysdate
, p_old_confirmation_code => p_old_confirmation_code
, p_registrant_account_id => p_registrant_account_id
, p_registrant_party_id => p_registrant_party_id
, p_registrant_contact_id => p_registrant_contact_id
, p_attendant_party_id => p_attendant_party_id
, p_attendant_contact_id => p_attendant_contact_id
, x_new_confirmation_code => x_new_confirmation_code
, x_new_system_status_code => x_new_system_status_code
, x_new_registration_id => x_new_registration_id
, x_return_status => l_return_status
, x_Msg_Count => x_msg_count
, x_Msg_Data => x_msg_data
);
transfer_insert( p_Api_Version_Number => p_api_version_number
, p_Init_Msg_List => p_init_msg_list
, p_Commit => p_commit
, p_old_offer_id => p_old_offer_id
, p_new_offer_id => p_new_offer_id
, p_system_status_code => l_system_status_code
, p_reg_status_date => sysdate
, p_old_confirmation_code => p_old_confirmation_code
, p_registrant_account_id => p_registrant_account_id
, p_registrant_party_id => p_registrant_party_id
, p_registrant_contact_id => p_registrant_contact_id
, p_attendant_party_id => p_attendant_party_id
, p_attendant_contact_id => p_attendant_contact_id
, x_new_confirmation_code => x_new_confirmation_code
, x_new_system_status_code => x_new_system_status_code
, x_new_registration_id => x_new_registration_id
, x_return_status => l_return_status
, x_Msg_Count => x_msg_count
, x_Msg_Data => x_msg_data
);
SELECT 1 FROM DUAL
WHERE EXISTS (select 1
from ams_event_registrations
where confirmation_code = conf_code_in
);
select
registrant_party_id,
registrant_contact_id,
attendant_party_id,
attendant_contact_id,
event_offer_id
from ams_event_registrations
where registrant_party_id = p_reg_party_id
and registrant_contact_id = p_reg_contact_id
and attendant_party_id = p_attendant_party_id
and attendant_contact_id = p_attendant_contact_id
and event_offer_id = p_event_offer_id;
x_evt_regs_rec.last_update_date := FND_API.g_miss_date;
x_evt_regs_rec.last_updated_by := FND_API.g_miss_num;
x_evt_regs_rec.last_update_login := FND_API.g_miss_num;
SELECT *
FROM ams_event_registrations
WHERE event_registration_id = p_evt_regs_rec.event_registration_id;
SELECT event_object_type,
source_code,
reg_start_date,
event_end_date,
owner_user_id
FROM ams_event_offers_all_b
WHERE event_offer_id = p_event_offer_id;
SELECT JTF_IH_MEDIA_ITEMS_S1.NEXTVAL
FROM dual;
SELECT jtf_ih_interactions_s1.NEXTVAL
FROM dual;
SELECT JTF_IH_ACTIVITIES_S1.NEXTVAL
FROM dual;
SELECT user_id
FROM ams_jtf_rs_emp_v
WHERE resource_id = p_resource_id;