The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 1
from ota_delegate_bookings tdb
where tdb.event_id = p_event_id
and tdb.customer_id = p_customer_id;
select event_type
from ota_events
where event_id = p_evt_id;
select 1
from hz_parties party, hz_cust_accounts cust_acct
where cust_acct.party_id = party.party_id
and cust_acct.cust_account_id = p_customer_id;
select 'X'
from ota_event_associations
where event_id = p_evt_id
and (organization_id is not null
or position_id is not null
or job_id is not null);
select 1
from ota_event_associations
where event_id = p_evt_id
and customer_id = p_cus_id
and (p_tea_id is null or event_association_id <> p_tea_id);
select 'X'
from ota_event_associations
where event_id = p_event_id
and customer_id is not null;
select null
from ota_event_associations
where event_id = p_event_id
and customer_id is null
and ((p_organization_id is null and
organization_id is null)
or (p_organization_id = organization_id))
and ((p_job_id is null and
job_id is null)
or (p_job_id = job_id))
and ((p_position_id is null and
position_id is null)
or (p_position_id = position_id))
and (p_event_association_id is null
or event_association_id <> p_event_association_id);
select null
from ota_events evt
where event_id = p_event_id
and (p_organization_id is null or exists
(select null
from hr_organization_units
where organization_id = p_organization_id
and business_group_id = evt.business_group_id))
and (p_job_id is null or exists
(select null
from per_jobs
where job_id = p_job_id
and business_group_id = evt.business_group_id))
and (p_position_id is null or exists
(select null
from per_positions
where position_id = p_position_id
and business_group_id = evt.business_group_id));
select null
from ota_events evt
where event_id = p_event_id
and (p_organization_id is null or exists
(select null
from hr_all_organization_units
where organization_id = p_organization_id
))
and (p_job_id is null or exists
(select null
from per_jobs
where job_id = p_job_id
))
and (p_position_id is null or exists
(select null
from per_all_positions
where position_id = p_position_id
));
select nvl(overdraft_limit,0)
from ota_booking_deals
where booking_deal_id = p_booking_deal_id;
select sum(nvl(money_amount,0))
from ota_finance_lines
where finance_header_id = p_finance_header_id
and booking_id is not null
and cancelled_flag <> 'Y'
and booking_deal_id <> p_booking_deal_id;
select sum(nvl(money_amount,0))
from ota_finance_lines
where booking_deal_id = p_booking_deal_id
and booking_id is null
and cancelled_flag <> 'Y';
select public_event_flag
from ota_events
where event_id = p_event_id;
select null
from ota_delegate_bookings tdb
where tdb.event_id = p_event_id
and exists
(select null
from ota_event_associations tea
where tea.event_id = p_event_id
and decode(tea.event_association_id,p_event_association_id
,p_customer_id,tea.customer_id) = tdb.customer_id);
select asg.organization_id,asg.job_id,asg.position_id
from ota_delegate_bookings tdb
, per_assignments_f asg
, ota_events evt
where evt.event_id = p_event_id
and evt.event_id = tdb.event_id
and tdb.delegate_assignment_id = asg.assignment_id
and tdb.date_booking_placed between
asg.effective_start_date and asg.effective_end_date;
select null
from ota_event_associations tea
where event_id = p_event_id
and event_association_id <> p_event_association_id
and nvl(organization_id,-1) = decode(organization_id,null,-1
,nvl(l_organization_id,-1))
and nvl(job_id,-1) = decode(job_id,null,-1
,nvl(l_job_id,-1))
and nvl(position_id,-1) = decode(position_id,null,-1
,nvl(l_position_id,-1));
procedure delete_check_tdb (
p_event_association_id in number
)
is
---------------
cursor csr_combination is
select event_id, customer_id
from ota_event_associations
where event_association_id = p_event_association_id;
end delete_check_tdb;
select ple.price
from ota_price_lists tpl,
ota_price_list_entries ple,
ota_events evt,
ota_vendor_supplies vsp
where
evt.event_id = p_event_id
and tpl.business_group_id = p_business_group_id
and tpl.currency_code = p_currency_code
and (
(p_booking_deal_type = 'P' and tpl.price_list_type = 'T')
or
(p_booking_deal_type <> 'P' and tpl.price_list_type = 'M')
or
(p_booking_deal_type is null and tpl.price_list_type = 'M')
)
and ple.price_list_id = tpl.price_list_id
and vsp.vendor_supply_id(+) = ple.vendor_supply_id
and ( evt.activity_version_id = ple.activity_version_id
or evt.activity_version_id = vsp.vendor_supply_id )
and ple.price_basis = 'C'
and p_customer_total_delegates between ple.minimum_attendees and ple.maximum_attendees
and (
(evt.course_start_date between ple.start_date and nvl(ple.end_date,hr_api.g_eot))
or
(evt.course_start_date is null and
p_session_date between ple.start_date and nvl(ple.end_date,hr_api.g_eot))
);
select sum(a.number_of_places)
from ota_delegate_bookings a
, ota_booking_status_types b
where a.booking_status_type_id = b.booking_status_type_id
and a.customer_id = p_customer
and a.event_id = p_event
and a.delegate_contact_id is null;
select null
from ota_price_list_entries ple,
ota_events evt,
ota_price_lists_v tpl
where evt.event_id = p_event_id
and tpl.business_group_id = p_business_group_id
and tpl.currency_code = evt.currency_code
and tpl.price_list_id = ple.price_list_id
and ple.price_basis = 'C'
and ple.activity_version_id = evt.activity_version_id
and p_customer_total_delegates
between ple.minimum_attendees
and ple.maximum_attendees
and p_customer_total_delegates_old
not between ple.minimum_attendees
and ple.maximum_attendees
and (
(evt.course_start_date
between ple.start_date
and nvl(ple.end_date,evt.course_start_date)
)
or
(evt.course_start_date is null
and p_session_date
between ple.start_date
and nvl(ple.end_date,p_session_date)
)
)
and (
(p_booking_deal_type is null
and tpl.price_list_type = 'M')
or
(p_booking_deal_type is not null
and exists (select null
from ota_booking_deals tbd
where tbd.booking_deal_id = p_booking_deal_id
and (tbd.price_list_id is null
or (tbd.price_list_id is not null
and tbd.price_list_id = tpl.price_list_id
)
)
)
)
)
order by tpl.name;
Procedure insert_validate(p_rec in ota_tea_shd.g_rec_type
,p_association_type in varchar2) is
--
l_proc varchar2(72) := g_package||'insert_validate';
select price_basis into l_price_basis from ota_events where event_id = p_rec.event_id;
End insert_validate;
Procedure update_validate(p_rec in ota_tea_shd.g_rec_type
,p_association_type in varchar2) is
--
l_proc varchar2(72) := g_package||'update_validate';
select price_basis into l_price_basis from ota_events where event_id = p_rec.event_id;
End update_validate;
Procedure delete_validate(p_rec in ota_tea_shd.g_rec_type) is
--
l_proc varchar2(72) := g_package||'delete_validate';
delete_check_tdb(p_event_association_id => p_rec.event_association_id);
End delete_validate;