The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure chk_non_updateable_args
(
p_rec in ota_tdb_shd.g_rec_type
) IS
--
l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
End chk_non_updateable_args;
select pbg.security_group_id,
pbg.legislation_code
from per_business_groups_perf pbg
, ota_delegate_bookings tdb
where tdb.booking_id = p_booking_id
and pbg.business_group_id = tdb.business_group_id;
select pbg.legislation_code
from per_business_groups_perf pbg
, ota_delegate_bookings tdb
where tdb.booking_id = p_booking_id
and pbg.business_group_id = tdb.business_group_id;
select type
from ota_booking_status_types
where booking_status_type_id = p_booking_status_type_id;
select 1
from per_all_people_f
where person_id = p_person_id
and p_date between effective_start_date and effective_end_date;
select 1
from HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
HZ_RELATIONSHIPS REL,
HZ_CUST_ACCOUNTS ROLE_ACCT
where ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID
AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_person_id;
select legislation_code
from per_business_groups
where business_group_id=p_business_group_id;
SELECT paf.ROWID
FROM per_all_assignments_f paf, ota_events evt
WHERE paf.assignment_id = p_assignment_id
AND evt.event_id = p_event_id
AND (
evt.event_status = 'P'
AND evt.enrolment_start_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
OR (
evt.event_type = 'PROGRAMME'
AND trunc(p_date_booking_placed) BETWEEN paf.effective_start_date
AND paf.effective_end_date)
OR (
-- Modified for Bug#3596070
trunc(p_date_booking_placed) --evt.course_start_date
BETWEEN paf.effective_start_date AND paf.effective_end_date) );
SELECT paf.ROWID
FROM per_all_assignments_f paf, ota_events evt ,
per_all_people_f ppf,
per_person_type_usages_f ptu,
per_person_types ppt
WHERE paf.person_id = l_person_id
AND ppf.person_id = l_person_id
AND ptu.person_id = ppf.person_id
AND evt.event_id = p_event_id
AND (
evt.event_status = 'P'
AND evt.enrolment_start_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
OR (
evt.event_type = 'PROGRAMME'
AND trunc(p_date_booking_placed) BETWEEN paf.effective_start_date
AND paf.effective_end_date)
OR (
-- Modified for Bug#3596070
trunc(p_date_booking_placed) --evt.course_start_date
BETWEEN paf.effective_start_date AND paf.effective_end_date) )
AND (
( evt.event_status = 'P'
AND evt.enrolment_start_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND evt.enrolment_start_date BETWEEN ptu.effective_start_date
AND ptu.effective_end_date)
OR (
evt.event_type = 'PROGRAMME'
AND trunc(p_date_booking_placed) BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND trunc(p_date_booking_placed) BETWEEN ptu.effective_start_date
AND ptu.effective_end_date)
OR (
-- Modified for Bug#3596070
trunc(p_date_booking_placed) --evt.course_start_date
BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND
-- Modified for Bug#3596070
trunc(p_date_booking_placed) --evt.course_start_date
BETWEEN ptu.effective_start_date AND ptu.effective_end_date ) )
AND ppt.business_group_id = ppf.business_group_id
AND ppf.business_group_id = paf.business_group_id
AND ptu.person_type_id = ppt.person_type_id
AND ppt.system_person_type in ('EMP','CWK','APL') ; -- Added 'APL' for 3885568
SELECT paf.ROWID
FROM per_all_assignments_f paf
WHERE paf.assignment_id = p_assignment_id
AND trunc(p_date_booking_placed) BETWEEN paf.effective_start_date
AND paf.effective_end_date;
SELECT paf.ROWID
FROM per_all_assignments_f paf ,
per_all_people_f ppf,
per_person_type_usages_f ptu,
per_person_types ppt
WHERE paf.person_id = l_person_id
AND ppf.person_id = l_person_id
AND ptu.person_id = ppf.person_id
AND trunc(p_date_booking_placed) BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND trunc(p_date_booking_placed) BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND trunc(p_date_booking_placed) BETWEEN ptu.effective_start_date
AND ptu.effective_end_date
AND ppt.business_group_id = ppf.business_group_id
AND ppf.business_group_id = paf.business_group_id
AND ptu.person_type_id = ppt.person_type_id
AND ppt.system_person_type in ('EMP','CWK','APL') ; -- Added 'APL' for 3885568
select person_id ,rowid
from per_all_assignments_f
where assignment_id = p_assignment_id ;
select maximum_internal_attendees
from ota_events
where event_id = p_event_id;
select sum(a.number_of_places)
from ota_delegate_bookings a,
ota_booking_status_types b
where a.event_id = p_event_id
and a.booking_status_type_id = b.booking_status_type_id
and b.type in ('P','A','E') --6683076.Added new enrollment status.
and a.internal_booking_flag = 'Y'
and a.booking_id <> nvl(p_booking_id, hr_api.g_number);
insert into ota_booking_status_histories
(booking_id,
booking_status_type_id,
start_date,
changed_by,
comments,
object_version_number)
values (p_booking_id,
p_previous_status_type_id,
p_date_booking_placed,
p_created_by,
'Enrolled',
1);
insert into ota_booking_status_histories
(booking_id,
booking_status_type_id,
start_date,
changed_by,
comments,
object_version_number)
values
(p_booking_id,
p_booking_status_type_id,
l_date_changed,
p_administrator,
p_status_change_comments,
1);
select 'X'
from ota_resource_allocations
where booking_id = p_booking_id;
select nvl(sum(booking_id),0)
from ota_finance_lines tfl
where tfl.booking_id = p_booking_id;
select 'Y'
from OTA_TRAINING_PLAN_COSTS
where booking_id = p_booking_id;
select 'X'
from ota_booking_status_types
where booking_status_type_id = p_booking_status_type_id
and business_group_id = p_business_group_id;
select event_type
from ota_events
where event_id = p_event_id;
select booking_id
from ota_delegate_bookings
where event_id = p_event_id
and delegate_contact_id = p_person_id;
select booking_id
from ota_delegate_bookings
where event_id = p_event_id
and delegate_person_id = p_person_id;
select bst.type
from ota_delegate_bookings tdb,
ota_booking_status_types bst
where tdb.booking_id = l_result
and bst.booking_status_type_id = tdb.booking_status_type_id;
select bst.type
from ota_delegate_bookings tdb,
ota_booking_status_types bst
where tdb.booking_id = l_result
and bst.booking_status_type_id = tdb.booking_status_type_id;
select nvl(sum(booking_id),0)
from ota_finance_lines tfl
where tfl.booking_id = p_booking_id
and tfl.cancelled_flag = p_cancelled_flag;
SELECT 1
FROM ota_events e
WHERE
-- Added for bug#5169098
ota_timezone_util.convert_date(trunc(sysdate), to_char(sysdate,'HH24:MI'), ota_timezone_util.get_server_timezone_code, e.timezone)
>= ota_timezone_util.convert_date(nvl(e.course_start_date,to_date('0001/01/01','YYYY/MM/DD')), course_start_time, e.timezone, e.timezone)
--(sysdate >= nvl( e.course_start_date, sysdate))
AND e.event_id = p_event_id;
SELECT name
FROM ota_booking_status_types bst
WHERE bst.booking_status_type_id = p_booking_status_type_id
AND bst.type in ('A','E'); --- bug 9009925
select count(event_association_id)
from ota_event_associations
where event_id = p_event_id;
select course_start_date
from ota_events
where event_id = p_event_id;
select 'X'
from ota_events b
where b.event_id = p_event_id
and public_event_flag = 'N';
select 'X'
from ota_event_associations
where (event_id = p_event_id
and ((p_customer_id is not null and customer_id = p_customer_id)
or (l_party_id is not null and party_id =l_party_id))); /* bug 3463908 */
or not exists (select null
from ota_event_associations evt
where evt.event_id = p_event_id);*/
select organization_id, job_id, position_id
from per_assignments_f
where assignment_id = p_delegate_assignment_id
and NVL(l_start_date,TRUNC(sysdate))
between effective_start_date
and effective_end_date;
select 'Y'
from ota_event_associations
where (event_id = p_event_id
and nvl(organization_id,-1) = decode(organization_id,null,-1,nvl(l_organization_id,-1))
and nvl(position_id,-1) = decode(position_id,null,-1,nvl(l_position_id,-1))
and nvl(job_id,-1) = decode(job_id,null,-1,nvl(l_job_id,-1)))
or not exists (select null
from ota_event_associations evt
where evt.event_id = p_event_id);
select price_basis
from ota_events
where event_id = p_event_id;
SELECT party.party_id
FROM HZ_CUST_ACCOUNT_ROLES acct_role,
HZ_PARTIES party,
HZ_RELATIONSHIPS rel,
HZ_ORG_CONTACTS org_cont,
HZ_PARTIES rel_party,
HZ_CUST_ACCOUNTS role_acct
WHERE acct_role.party_id = rel.party_id
AND acct_role.role_type = 'CONTACT'
AND org_cont.party_relationship_id = rel.relationship_id
AND rel.subject_id = party.party_id
AND rel.party_id = rel_party.party_id
AND rel.subject_table_name = 'HZ_PARTIES'
AND rel.object_table_name = 'HZ_PARTIES'
AND acct_role.cust_account_id = role_acct.cust_account_id
AND role_acct.party_id = rel.object_id
AND ACCT_ROLE.cust_account_role_id = p_delegate_contact_id;
Select public_event_flag, maximum_internal_attendees, course_start_date, parent_offering_id
From ota_events
Where event_id = p_event_id;
Select public_event_flag, maximum_internal_attendees, course_start_date, parent_offering_id
into l_public_event_flag, l_max_internal, l_event_start_date, l_parent_offering_id
From ota_events
Where event_id = p_event_id;
select nvl(sum(db.number_of_places),0)
from ota_delegate_bookings db,
ota_booking_status_types bst
where bst.booking_status_type_id = nvl(p_booking_status_type_id,
bst.booking_status_type_id)
and bst.type = nvl(p_status_type, bst.type)
and (p_usage_type is null or
ota_tdb_bus.event_place_needed(bst.booking_status_type_id) = 1)
and bst.booking_status_type_id = db.booking_status_type_id
and (p_booking_id is null or
p_booking_id is not null and db.booking_id <> p_booking_id)
and db.internal_booking_flag = decode(p_all_or_internal,
'INTERNAL','Y',
db.internal_booking_flag)
and db.event_id = p_event_id;
select 'X'
from ota_delegate_bookings
where customer_id = p_customer_id
and event_id in (select a.program_event_id
from ota_program_memberships a
where a.event_id = p_event_id)
and (
(p_booking_id is not null and booking_id <> p_booking_id
)
or
p_booking_id is null
)
and (delegate_contact_id = p_delegate_contact_id
or
(delegate_contact_id is null and p_delegate_contact_id is null
)
);
select 'X'
from ota_delegate_bookings
where event_id in (select a.program_event_id
from ota_program_memberships a
where a.event_id = p_event_id)
and (
(p_booking_id is not null and booking_id <> p_booking_id
)
or
p_booking_id is null
)
and (delegate_person_id = p_delegate_person_id
or
(delegate_person_id is null and p_delegate_person_id is null
)
);
select null
from ota_program_memberships a
where a.event_id = p_event_id;
select type
from ota_booking_status_types
where booking_status_type_id = p_booking_status_type_id;
select enrolment_start_date
from ota_events
where event_id = nvl(g_event_rec.parent_event_id, -1);
select enrolment_start_date,
enrolment_end_date,
timezone
from ota_events
where event_id = nvl(g_event_rec.parent_event_id, -1);
select enrolment_start_date,
enrolment_end_date,
timezone,
event_type
from ota_events
where event_id = p_event_id;
select per_letter_requests_s.nextval
from dual;
insert into per_letter_requests
(letter_request_id
,business_group_id
,letter_type_id
,date_from
,request_status
,auto_or_manual
,event_id)
select l_request_id
, a.business_group_id
, p_letter_type_id
, sysdate
, 'PENDING'
, 'AUTO'
, p_event_id
from per_letter_types a
where a.letter_type_id = p_letter_type_id;
select letter_request_id
from per_letter_requests
where letter_type_id = p_letter_type_id
and request_status = 'PENDING';
select per_letter_requests_s.nextval
from dual;
insert into per_letter_requests
(letter_request_id
,business_group_id
,letter_type_id
,date_from
,request_status
,auto_or_manual)
select l_request_id
, a.business_group_id
, p_letter_type_id
, sysdate
, 'PENDING'
, 'AUTO'
from per_letter_types a
where a.letter_type_id = p_letter_type_id;
select b.letter_type_id,
c.letter_request_id
from per_letter_gen_statuses a,
per_letter_types b,
per_letter_requests c
where a.assignment_status_type_id = p_booking_status_type_id
and b.letter_type_id = a.letter_type_id
and b.generation_status_type = 'OTA_BOOKING'
and c.letter_type_id = b.letter_type_id
and c.event_id= p_event_id
and c.request_status = 'PENDING'
and c.auto_or_manual = 'AUTO'
and a.enabled_flag='Y'; ---***added for bug#2791524;
select b.letter_type_id,
c.letter_request_id
from per_letter_gen_statuses a,
per_letter_types b,
per_letter_requests c
where a.assignment_status_type_id = p_booking_status_type_id
and b.letter_type_id = a.letter_type_id
and b.generation_status_type = 'OTA_BOOKING'
and c.letter_type_id = b.letter_type_id
AND c.event_id IS null
and c.request_status = 'PENDING'
and c.auto_or_manual = 'AUTO'
and a.enabled_flag='Y'; ---***added for bug#2791524;
select a.letter_type_id
from per_letter_gen_statuses a,
per_letter_types b
where a.assignment_status_type_id = cp_status_id
and b.letter_type_id = a.letter_type_id
and b.generation_status_type = 'OTA_BOOKING'
and a.enabled_flag='Y'; ---***added for bug#2791524;
select null
from per_letter_request_lines
where ota_booking_id = p_booking_id
and ota_booking_status_type_id = p_booking_status_type_id
and letter_request_id = l_letter_request_id;
select business_group_id
from ota_events
where event_id = p_event_id;
insert into per_letter_request_lines
(letter_request_line_id,
business_group_id,
letter_request_id,
person_id,
ota_booking_id,
ota_booking_status_type_id,
date_from
)
values
(per_letter_request_lines_s.nextval,
l_business_group_id,
l_letter_req_id,
p_delegate_person_id,
p_booking_id,
p_booking_status_type_id,
trunc(sysdate)
);
select a.letter_type_id
, a.business_group_id
from per_letter_gen_statuses a
, per_letter_types b
where a.assignment_status_type_id = cp_status_id
and b.letter_type_id = a.letter_type_id
and b.generation_status_type = 'OTA_BOOKING';
select letter_request_id
from per_letter_requests
where letter_type_id = p_letter_type_id
and request_status = 'PENDING'
and auto_or_manual = 'AUTO';
select null
from per_letter_request_lines
where ota_booking_id = p_booking_id
and ota_booking_status_type_id = p_booking_status_type_id
and letter_request_id = l_letter_request_id;
insert into per_letter_request_lines
(letter_request_line_id,
business_group_id,
letter_request_id,
ota_booking_id,
ota_booking_status_type_id,
date_from
)
values
(per_letter_request_lines_s.nextval,
r_letters.business_group_id,
l_letter_request_id,
p_booking_id,
p_booking_status_type_id,
trunc(sysdate)
);
select 1
from ota_delegate_bookings tdb,
ota_program_memberships pmm
where tdb.event_id = pmm.event_id
and pmm.program_event_id = l_event_id
and tdb.delegate_person_id = l_delegate_person_id;
select 1
from ota_delegate_bookings tdb,
ota_program_memberships pmm
where tdb.event_id = pmm.event_id
and pmm.program_event_id = l_event_id
and tdb.delegate_contact_id = l_delegate_contact_id;
select null
from oe_order_lines_all
where line_id = p_line_id;
Select LINE_ID
FROM OTA_EVENTS
WHERE EVENT_ID = p_event_id
AND LINE_ID IS NOT NULL ;
SELECT organization_id
FROM ota_events
WHERE event_id = p_event_id
AND nvl(secure_event_flag,'N') = 'Y';
SELECT oft.learning_object_id
,ctu.online_flag
,evt.offering_id
FROM ota_offerings oft
, ota_events evt
, ota_category_usages ctu
WHERE oft.offering_id = evt.parent_offering_id
AND ctu.category_usage_id = oft.delivery_mode_id
AND evt.event_id = p_class_id;
SELECT lesson_status
FROM ota_performances
WHERE learning_object_id = p_lo_id
AND user_id = p_user_id
AND user_type = p_user_type
AND lesson_status IN ('P', 'C');
Procedure insert_validate(
p_rec in ota_tdb_shd.g_rec_type,
p_enrollment_type in varchar2
) is
--
l_proc varchar2(72) := g_package||'insert_validate';
End insert_validate;
Procedure update_validate(
p_rec in ota_tdb_shd.g_rec_type,
p_enrollment_type in varchar2
) is
--
l_proc varchar2(72) := g_package||'update_validate';
SELECT nvl(avt.inventory_item_id,1)
FROM ota_events evt, ota_activity_versions avt
WHERE evt.activity_version_id = avt.activity_version_id
AND evt.event_id = p_event_id ;
chk_non_updateable_args
(
p_rec => p_rec
);
End update_validate;
Procedure delete_validate(p_rec in ota_tdb_shd.g_rec_type) is
--
l_proc varchar2(72) := g_package||'delete_validate';
End delete_validate;