APPS.OTA_TSR_BUS SQL Statements

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

Line: 53

    select pbg.security_group_id,
           pbg.legislation_code
      from per_business_groups_perf pbg
         , ota_suppliable_resources tsr
     where tsr.supplied_resource_id = p_supplied_resource_id
       and pbg.business_group_id = tsr.business_group_id;

Line: 245

	select 1
	from ota_suppliable_resources_vl
	where business_group_id = p_biz
	and (p_suppres_id is null or p_suppres_id <> supplied_resource_id)
        and name = p_name
	and resource_type = p_resource_type;

Line: 353

Procedure check_type_update (
   p_tsr_id	in number,
   p_ovn	in number,
   p_resource_type in varchar2) is
---------------
  l_proc        varchar2(72) := g_package||'check_type_update';

Line: 385

End check_type_update;

Line: 399

    select 'X'
    from ota_resource_usages
    where supplied_resource_id = p_supplied_resource_id
      and (start_date < p_start_date or
           nvl(end_date, hr_api.g_eot) > nvl(p_end_date, hr_api.g_eot));

Line: 435

    select 'X'
    from ota_resource_bookings
    where supplied_resource_id = p_supplied_resource_id
      and (required_date_from < p_start_date
           or
           nvl(required_date_to,hr_api.g_eot) > nvl(p_end_date,hr_api.g_eot));

Line: 562

    select 'X'
    from ota_resource_bookings
    where supplied_resource_id = p_supplied_resource_id;

Line: 594

    select 'X'
    from ota_resource_usages
    where supplied_resource_id = p_supplied_resource_id;

Line: 637

  Select null
  From HR_ALL_ORGANIZATION_UNITS
  Where organization_id = p_training_center_id;

Line: 686

  Select null
  From HR_LOCATIONS_ALL loc
  Where loc.location_id = p_location_id ;

Line: 744

  Select null
  From PER_ALL_PEOPLE_F per, PER_PERSON_TYPE_USAGES_F ptu, PER_PERSON_TYPES ppt
  Where per.person_id = p_trainer_id
  --AND   trunc(p_start_date) between per.effective_start_date and per.effective_end_date Bug# 2956482
  AND   trunc(p_start_date) >= per.start_date
  AND   NVL(trunc(p_end_date),trunc(sysdate)) <= ptu.effective_end_date  --bug no 3058027
  AND	trunc(p_start_date) between ptu.effective_start_date and ptu.effective_end_date
  AND	ptu.person_id = per.person_id
  AND	ppt.business_group_id = per.business_group_id
 -- AND	ppt.system_person_type in ('EMP','CWK') Bug# 2956482
  AND    ((ppt.system_person_type = 'EMP' and nvl(per.current_employee_flag,'N')='Y')
        or (ppt.system_person_type = 'CWK'  and nvl(per.current_npw_flag,'N')='Y'))
  AND	ptu.person_type_id = ppt.person_type_id ;

Line: 760

  Select null
  From PER_ALL_PEOPLE_F per, PER_PERSON_TYPE_USAGES_F ptu, PER_PERSON_TYPES ppt
  Where per.person_id = p_trainer_id
 -- AND   trunc(p_start_date) between per.effective_start_date and per.effective_end_date Bug# 2956482
  AND   trunc(p_start_date) >= per.start_date
  AND   NVL(trunc(p_end_date),trunc(sysdate)) <= ptu.effective_end_date  --bug no 3058027
  AND	trunc(p_start_date) between ptu.effective_start_date and ptu.effective_end_date
  AND	ptu.person_id = per.person_id
  AND	ppt.business_group_id = per.business_group_id
  AND	per.business_group_id = p_business_group_id
 -- AND	ppt.system_person_type in ('EMP','CWK') Bug# 2956482
  AND    ((ppt.system_person_type = 'EMP' and nvl(per.current_employee_flag,'N')='Y')
         or (ppt.system_person_type = 'CWK' and nvl(per.current_npw_flag,'N')='Y'))
  AND	ptu.person_type_id = ppt.person_type_id  ;

Line: 1024

procedure insert_validate2
(
p_rec in ota_tsr_shd.g_rec_type
)
is
---------------------
begin
--
validation1(p_rec);

Line: 1036

end insert_validate2;

Line: 1039

procedure update_validate2
(
p_rec in ota_tsr_shd.g_rec_type
)
is
---------------------
begin
--
check_type_update(
   p_tsr_id		=> p_rec.supplied_resource_id,
   p_ovn		=> p_rec.object_version_number,
   p_resource_type	=> p_rec.resource_type);

Line: 1061

end update_validate2;

Line: 1064

procedure delete_validate2
(
p_rec in ota_tsr_shd.g_rec_type
)
is
---------------------
begin
--
del_check_bookings(p_rec.supplied_resource_id);

Line: 1076

end delete_validate2;

Line: 1083

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

Line: 1091

	insert_validate2(p_rec);

Line: 1097

End insert_validate;

Line: 1102

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

Line: 1110

	update_validate2(p_rec);

Line: 1115

End update_validate;

Line: 1120

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

Line: 1128

	delete_validate2(p_rec);

Line: 1131

End delete_validate;

Line: 1172

          select legislation_code
          from   per_business_groups_perf pbg,
                 ota_suppliable_resources tsr
          where  pbg.business_group_id    = tsr.business_group_id
            and  tsr.supplied_resource_id = p_supplied_resource_id;