eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OTA_TEA_BUS SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 18


 	select 1
	from ota_delegate_bookings tdb
	where tdb.event_id = p_event_id
	  and tdb.customer_id = p_customer_id;

Line: 53


        select event_type
        from ota_events
        where event_id = p_evt_id;

Line: 93


	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;

Line: 135


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);

Line: 143


	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);

Line: 195


select 'X'
from ota_event_associations
where event_id = p_event_id
and   customer_id is not null;

Line: 201


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);

Line: 218


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));

Line: 239


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
       ));

Line: 347


    select nvl(overdraft_limit,0)
    from   ota_booking_deals
    where  booking_deal_id = p_booking_deal_id;

Line: 352


    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;

Line: 360


    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';

Line: 451


select public_event_flag
from ota_events
where event_id = p_event_id;

Line: 480


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);

Line: 491


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;

Line: 504


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));

Line: 566


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;

Line: 606


end delete_check_tdb;

Line: 623


    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))
	);

Line: 694


      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;

Line: 738


    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;

Line: 804


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';

Line: 822


     select price_basis into l_price_basis from ota_events where event_id = p_rec.event_id;

Line: 840


End insert_validate;

Line: 845


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';

Line: 881


     select price_basis into l_price_basis from ota_events where event_id = p_rec.event_id;

Line: 908


End update_validate;

Line: 913


Procedure delete_validate(p_rec in ota_tea_shd.g_rec_type) is
--
  l_proc  varchar2(72) := g_package||'delete_validate';

Line: 922


     delete_check_tdb(p_event_association_id => p_rec.event_association_id);

Line: 925


End delete_validate;