APPS.PQH_PSF_BUS SQL Statements

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

Line: 27

select 'x'
from dual
where exists (
select null
from per_all_assignments_f asg, per_assignment_status_types ast
where asg.position_id = p_position_id
and asg.assignment_type in ('E', 'C','A') -- changes made for the bug 5680305
and asg.assignment_status_type_id = ast.assignment_status_type_id
and ast.per_system_status <> 'TERM_ASSIGN'
and ((asg.effective_start_date between p_validation_start_date
      and p_validation_end_date) or
      (asg.effective_end_date between p_validation_start_date
      and p_validation_end_date) or
      (asg.effective_start_date <= p_validation_start_date
      and effective_end_date >=p_validation_end_date))
);

Line: 62

procedure hr_psf_bus_insert_validate(p_rec 			 in hr_psf_shd.g_rec_type
     ,p_effective_date	       in date
     ) is
l_chk_position_job_grade        boolean;

Line: 69

select transaction_status
from pqh_position_transactions
where position_transaction_id = p_position_transaction_id;

Line: 95

procedure hr_psf_bus_update_validate(p_rec in hr_psf_shd.g_rec_type
      ,p_effective_date	       in date
      ,p_validation_start_date in date
      ,p_validation_end_date    in date
     ,p_datetrack_mode	       in varchar2
     ) is
--
--
l_chk_seasonal                  boolean;

Line: 114

select transaction_status
from pqh_position_transactions
where position_transaction_id = p_position_transaction_id;

Line: 121

select  max(pqh_psf_bus.sum_assignment_fte(p_position_id,ed))
from
(select a.effective_start_date ed
from per_all_assignments_f a
where a.position_id = p_position_id
and ((a.effective_start_date between p_validation_start_date and p_validation_end_date)
    and ((a.effective_end_date between p_validation_start_date and p_validation_end_date)
        ))
union
select  a.effective_end_date ed
from per_all_assignments_f a
where a.position_id = p_position_id
and ((a.effective_start_date between p_validation_start_date and p_validation_end_date)
    and ((a.effective_end_date between p_validation_start_date and p_validation_end_date)
        ))
union
select p_validation_start_date  ed
from dual
union
select p_validation_end_date ed
from dual);

Line: 160

  if ((p_datetrack_mode IN ('CORRECTION', 'UPDATE','UPDATE_CHANGE_INSERT',
       'UPDATE_OVERRIDE')) AND
      ('ELIMINATED'=hr_psf_shd.get_availability_status(
        p_rec.availability_status_id,p_rec.business_group_id))) then
    hr_utility.set_location('AVAILABILITY STATUS : ELIMINATED', 621);

Line: 228

PROCEDURE per_asg_bus_insert_validate(p_rec 	per_asg_shd.g_rec_type
      ,p_effective_date	       in date) IS
l_bgt_lt_abv_fte    boolean := false;

Line: 248

select *
from hr_all_positions
where position_id = p_rec.position_id;

Line: 253

select min(effective_start_date)
from per_all_assignments_f
where position_id = p_position_id
and effective_start_date > p_effective_date;

Line: 259

 hr_utility.set_location('Insert Validate: Before open Position', 100);

Line: 262

  hr_utility.set_location('Insert Validate: After open before fetch Position', 110);

Line: 264

  hr_utility.set_location('Insert Validate: After fetch Position', 120);

Line: 270

    hr_utility.set_location('Insert Validate: Before Seasonal Validation', 130);

Line: 283

    hr_utility.set_location('Insert Validate: Before Assignment Grade', 130);

Line: 291

    hr_utility.set_location('Insert Validate: greater than earliest hire date', 130);

Line: 299

    hr_utility.set_location('Insert Validate: before proposed date for Layoff', 130);

Line: 307

    hr_utility.set_location('Insert Validate: SHARED position has FTE greater than', 130);

Line: 337

   hr_utility.set_location('Insert Validate:Check Insert allowed', 130);

Line: 339

      hr_utility.set_location('Insert Validate:POOLED', 130);

Line: 346

      hr_utility.set_location('Insert Validate:END POOLED', 130);

Line: 348

      hr_utility.set_location('Insert Validate:SINGLE-SHARED', 130);

Line: 350

        hr_utility.set_location('Insert Validate:OPEN', 130);

Line: 385

      hr_utility.set_location('Insert Validate:SINGLE-SHARED', 130);

Line: 407

 hr_utility.set_location('per_asg_insert_validate End',400);

Line: 414

PROCEDURE per_asg_bus_update_validate(p_rec 	per_asg_shd.g_rec_type
      ,p_effective_date	       in date
      ,p_validation_start_date in date
      ,p_validation_end_date    in date
      ,p_datetrack_mode	       in varchar2 ) IS
l_bgt_lt_abv_fte	    boolean := false;

Line: 437

select *
from hr_all_positions
where position_id = p_rec.position_id;

Line: 442

select *
from per_all_assignments
where assignment_id = p_rec.assignment_id;

Line: 447

select min(effective_start_date)
from per_all_assignments_f
where position_id = p_position_id
and effective_start_date > p_effective_date;

Line: 513

    hr_utility.set_location('Insert Validate: SHARED position has FTE greater than', 130);

Line: 616

PROCEDURE per_asg_bus_delete_validate(p_rec 	per_asg_shd.g_rec_type
      ,p_effective_date	       in date
      ,p_validation_start_date in date
      ,p_validation_end_date    in date
      ,p_datetrack_mode	       in varchar2 ) IS
l_position_id  number;

Line: 636

select effective_start_date, business_group_id
from per_all_assignments_f
where position_id = p_position_id
and effective_start_date between p_validation_start_date and p_validation_end_date
union
select effective_end_date, business_group_id
from per_all_assignments_f
where position_id = p_position_id
and effective_end_date between p_validation_start_date and p_validation_end_date
union
select abv.effective_start_date, abv.business_group_id
from per_assignment_budget_values_f abv, per_all_assignments_f asg
where abv.assignment_id = asg.assignment_id
  and asg.position_id = p_position_id
  and abv.effective_start_date between p_validation_start_date and p_validation_end_date
  and asg.effective_start_date between p_validation_start_date and p_validation_end_date
union
select abv.effective_end_date, abv.business_group_id
from per_assignment_budget_values_f abv, per_all_assignments_f asg
where abv.assignment_id = asg.assignment_id
  and asg.position_id = p_position_id
  and abv.effective_end_date between p_validation_start_date and p_validation_end_date
  and asg.effective_end_date between p_validation_start_date and p_validation_end_date
union
select effective_start_date, business_group_id
from hr_all_positions_f
where position_id = p_position_id
and effective_start_date between p_validation_start_date and p_validation_end_date;

Line: 666

select position_id
from per_all_assignments_f
where assignment_id = p_assignment_id
and p_effective_date between effective_start_date and effective_end_date;

Line: 672

select overlap_period, fte, organization_id
from hr_all_positions_f
where position_id = p_position_id
  and p_date between effective_start_date and effective_end_date;

Line: 678

  hr_utility.set_location('Entering pqh_asg_bus_delete_validate', 10);

Line: 693

  if p_datetrack_mode in ('DELETE_NEXT_CHANGE', 'FUTURE_CHANGE') then
     for r1 in c_changed_dates(l_position_id, p_validation_start_date, p_validation_end_date)
     loop
        hr_utility.set_location('Effective Start Date : ' || r1.effective_start_date,50);

Line: 752

   hr_utility.set_location('Exiting pqh_asg_bus_delete_validate', 400);

Line: 760

PROCEDURE per_abv_insert_validate(
		p_assignment_id number,
		p_value number,
		p_unit varchar2,
		p_effective_date date) is
l_proc          varchar2(100) := 'per_abv_insert_validate';

Line: 783

select paf.position_id, paf.person_id, paf.business_group_id
from per_all_assignments_f paf
where paf.assignment_id = p_assignment_id
and p_effective_date between paf.effective_start_date and paf.effective_end_date;

Line: 789

select position_id, fte, max_persons, position_type, organization_id, overlap_period
from hr_all_positions_f psf
where position_id =
(select position_id
from per_all_assignments_f paf
where paf.assignment_id = p_assignment_id
and p_effective_date between paf.effective_start_date and paf.effective_end_date)
and p_effective_date between psf.effective_start_date and psf.effective_end_date;

Line: 799

select sum(value)
from per_assignment_budget_values_f abv, per_all_assignments_f asg
where abv.assignment_id = asg.assignment_id
and asg.position_id = p_position_id
and p_effective_date between asg.effective_start_date and asg.effective_end_date
and p_effective_date between abv.effective_start_date and abv.effective_end_date
and abv.unit in ('F', 'FTE');

Line: 880

        hr_utility.set_location('Insert Validate:SINGLE-SHARED', 130);

Line: 882

           hr_utility.set_location('Insert Validate:OPEN', 130);

Line: 911

PROCEDURE per_abv_update_validate(
		p_abv_id number,
		p_assignment_id number,
		p_value number,
		p_unit varchar2,
		p_effective_date date,
        p_validation_start_date date,
        p_validation_end_date  date,
        p_datetrack_mode    varchar2) is
l_proc              varchar2(100) := 'per_abv_update_validate';

Line: 940

select position_id, person_id, business_group_id
from per_all_assignments_f paf
where paf.assignment_id = p_assignment_id
and p_effective_date between paf.effective_start_date and paf.effective_end_date;

Line: 946

select position_id, fte, max_persons, position_type, organization_id, overlap_period
from hr_all_positions_f psf
where position_id =
(select position_id
from per_all_assignments_f paf
where paf.assignment_id = p_assignment_id
and p_effective_date between paf.effective_start_date and paf.effective_end_date)
and p_effective_date between psf.effective_start_date and psf.effective_end_date;

Line: 956

select sum(value)
from per_assignment_budget_values_f abv, per_all_assignments_f asg
where abv.assignment_id = asg.assignment_id
and asg.position_id = p_position_id
and p_effective_date between asg.effective_start_date and asg.effective_end_date
and p_effective_date between abv.effective_start_date and abv.effective_end_date
and abv.unit in ('FTE')
and asg.assignment_id <> nvl(p_assignment_id,-999);

Line: 1047

          hr_utility.set_location('Insert Validate:SINGLE-SHARED', 130);

Line: 1049

            hr_utility.set_location('Insert Validate:OPEN', 130);

Line: 1110

select sum(nvl(value,1))
from per_assignment_budget_values_f abv, per_all_assignments_f asn,
per_assignment_status_types ast
where abv.assignment_id(+) = asn.assignment_id
and p_effective_date between asn.effective_start_date and asn.effective_end_date
and p_effective_date between abv.effective_start_date and abv.effective_end_date
and asn.position_id = p_position_id
and asn.assignment_type in ('E', 'C')
and abv.unit(+) = 'FTE'
and asn.assignment_status_type_id = ast.assignment_status_type_id
and ast.per_system_status <> 'TERM_ASSIGN';

Line: 1152

select sum(nvl(value,1))
from per_assignment_budget_values_f abv, per_all_assignments_f asn,
per_assignment_status_types ast
where abv.assignment_id(+) = asn.assignment_id
and asn.assignment_id <> p_assignment_id
and p_effective_date between asn.effective_start_date and asn.effective_end_date
and p_effective_date between abv.effective_start_date and abv.effective_end_date
and asn.position_id = p_position_id
and asn.assignment_type in ('E', 'C')
and abv.unit(+) = 'FTE'
and asn.assignment_status_type_id = ast.assignment_status_type_id
and ast.per_system_status <> 'TERM_ASSIGN';

Line: 1192

select nvl(sum(nvl(value,1)),0)
from per_all_assignments_f asn,FND_SESSIONS SS,
per_assignment_budget_values_f abv, FND_SESSIONS SS2,
per_assignment_status_types ast
where abv.assignment_id(+) = asn.assignment_id
and SS.SESSION_ID = USERENV('sessionid')
and asn.EFFECTIVE_START_DATE <= SS.EFFECTIVE_DATE
and asn.EFFECTIVE_END_DATE >= SS.EFFECTIVE_DATE
and SS2.SESSION_ID(+) = USERENV('sessionid')
and abv.EFFECTIVE_START_DATE <= SS2.EFFECTIVE_DATE(+)
and abv.EFFECTIVE_END_DATE >= SS2.EFFECTIVE_DATE(+)
and asn.position_id = p_position_id
and asn.person_id = p_person_id
and asn.assignment_type in ('E', 'C')
and abv.unit(+) = 'FTE'
and asn.assignment_status_type_id = ast.assignment_status_type_id
and ast.per_system_status <> 'TERM_ASSIGN';

Line: 1211

select nvl(sum(nvl(value,1)),0)
from per_all_assignments_f asn,FND_SESSIONS SS,
per_assignment_budget_values_f abv, FND_SESSIONS SS2,
per_assignment_status_types ast
where abv.assignment_id(+) = asn.assignment_id
and SS.SESSION_ID = USERENV('sessionid')
and asn.EFFECTIVE_START_DATE <= SS.EFFECTIVE_DATE
and asn.EFFECTIVE_END_DATE >= SS.EFFECTIVE_DATE
and SS2.SESSION_ID(+) = USERENV('sessionid')
and abv.EFFECTIVE_START_DATE <= SS2.EFFECTIVE_DATE(+)
and abv.EFFECTIVE_END_DATE >= SS2.EFFECTIVE_DATE(+)
and asn.position_id = p_position_id
and asn.person_id = p_person_id
and asn.assignment_id <> p_ex_assignment_id
and asn.assignment_type in ('E', 'C')
and abv.unit(+) = 'FTE'
and asn.assignment_status_type_id = ast.assignment_status_type_id
and ast.per_system_status <> 'TERM_ASSIGN';

Line: 1259

select to_number(org_information2,'99999999.99')
from hr_organization_information
where org_information_context like 'Budget Value Defaults'
and organization_id = p_organization_id
and org_information1='FTE';

Line: 1282

select nvl(value,1)
from per_all_assignments_f asn,FND_SESSIONS SS,
per_assignment_budget_values_f abv, FND_SESSIONS SS2
where abv.assignment_id(+) = asn.assignment_id
and SS.SESSION_ID = USERENV('sessionid')
and asn.EFFECTIVE_START_DATE <= SS.EFFECTIVE_DATE
and asn.EFFECTIVE_END_DATE >= SS.EFFECTIVE_DATE
and SS2.SESSION_ID(+) = USERENV('sessionid')
and abv.EFFECTIVE_START_DATE <= SS2.EFFECTIVE_DATE(+)
and abv.EFFECTIVE_END_DATE >= SS2.EFFECTIVE_DATE(+)
and asn.assignment_id = p_assignment_id
and asn.assignment_type in ('E', 'C')
and abv.unit(+) = 'FTE';

Line: 1320

select position_transaction_id
from pqh_position_transactions
where position_id = nvl(p_position_id, -1)
and transaction_status in ('SUBMITTED','APPROVED');

Line: 1368

select availability_status_id, business_group_id, position_type, date_effective
from hr_all_positions_f
where position_id = p_position_id
and p_effective_date between effective_start_date and effective_end_date;

Line: 1445

select overlap_period, overlap_unit_cd
from hr_all_positions
where position_id = p_position_id;

Line: 1450

select fnd_date.canonical_to_date(poei_information3), fnd_date.canonical_to_date(poei_information4)
from per_position_extra_info
where information_type = 'PER_OVERLAP'
      and position_id = p_position_id;

Line: 1488

select fnd_date.canonical_to_date(poei_information3) poei_information3,
       nvl(fnd_date.canonical_to_date(poei_information4),
           hr_general.end_of_time) poei_information4,
       poei_information5, poei_information6, poei_information7
from per_position_extra_info
where position_id = p_position_id
and information_type = 'PER_RESERVED';

Line: 1561

select position_type, FTE from hr_all_positions_f
where position_id = p_position_id
and p_effective_date
  between effective_start_date and effective_end_date;

Line: 1567

select assignment_id
from per_all_assignments_f
where position_id = p_position_id
and p_effective_date
  between effective_start_date and effective_end_date
and assignment_type in ('E', 'C');

Line: 1631

select poei_information3
from per_position_extra_info
where position_id = p_position_id
and information_type = 'PER_FAMILY'
and poei_information3 in ('ACADEMIC','FACULTY');

Line: 1657

select poei_information3
from per_position_extra_info
where position_id = p_rec.position_id
and position_extra_info_id <> l_position_extra_info_id
and information_type = 'PER_FAMILY'
and poei_information3 in ('ACADEMIC','FACULTY');

Line: 1689

select 'x'
from per_position_extra_info
where p_assignment_start_date
      between fnd_date.canonical_to_date(poei_information3)
      and fnd_date.canonical_to_date(poei_information4)
      and position_id = p_position_id
      and information_type = 'PER_OVERLAP';

Line: 1721

select poei_information3,
       poei_information4
from per_position_extra_info
where position_id = p_position_id
and information_type = 'PER_SEASONAL';

Line: 1761

select 'X'
from hr_all_positions
where position_id = p_position_id
and seasonal_flag = 'Y';

Line: 1779

select 'X'
from per_position_extra_info
where position_id = l_position_id
and information_type = 'PER_SEASONAL';

Line: 1798

select 'X'
from per_position_extra_info
where position_id = l_position_id
and information_type = 'PER_OVERLAP';

Line: 1816

select 'x'
from per_all_assignments
where position_id = p_position_id
and assignment_type in ('E', 'C');

Line: 1834

select 'X'
from hr_all_positions
where position_id = p_position_id
and overlap_period is not null;

Line: 1871

select count(1)
from per_all_assignments
where position_id = p_position_id
and assignment_type in ('E', 'C');

Line: 1888

select count(1)
from per_all_assignments_f
where position_id = p_position_id
and assignment_type in  ('E', 'C')
and p_effective_date between effective_start_date and effective_end_date;

Line: 1904

select max_persons
from hr_all_positions
where position_id = p_position_id;

Line: 1917

select proposed_date_for_layoff
from hr_all_positions_f
where position_id = p_position_id;

Line: 1930

select fte/max_persons
from hr_all_positions
where position_id = p_position_id;

Line: 1943

select position_type
from hr_all_positions
where position_id = p_position_id;

Line: 1956

select entry_grade_id
from hr_all_positions
where position_id = p_position_id;

Line: 1969

select work_period_type_cd
from hr_all_positions
where position_id = p_position_id;

Line: 2006

select 'x'
from per_valid_grades
where job_id = p_job_id
and grade_id = p_position_grade_id;

Line: 2029

select min(effective_start_date)
from per_all_assignments_f
where position_id = p_position_id
and assignment_type in ('E', 'C');

Line: 2043

select max(effective_start_date)
from per_all_assignments_f
where position_id = p_position_id
and assignment_type in ('E', 'C');

Line: 2078

cursor c1 is select system_type_cd
             from per_shared_types
             where shared_type_id = p_availability_status_id;

Line: 2109

   select business_group_id
   from hr_all_positions_f
   where position_id = p_position_id;

Line: 2113

   cursor c1(p_unit_id number) is select system_type_cd from
       per_shared_types where shared_type_id = p_unit_id;

Line: 2115

   cursor c2(p_budget_id number) is select bdt.budget_detail_id
                from  pqh_budget_details bdt,pqh_budget_versions bvr
                where bvr.budget_id = p_budget_id
                and p_effective_date between bvr.date_from and nvl(bvr.date_to,p_effective_date)
                and bdt.budget_version_id = bvr.budget_version_id
                and bdt.position_id = p_position_id;

Line: 2122

                select bpr.budget_unit1_value, bpr.budget_unit2_value, bpr.budget_unit3_value
                from pqh_budget_periods bpr, per_time_periods tp_s,
			per_time_periods tp_e
                where bpr.budget_detail_id = p_budget_detail_id
                and tp_s.time_period_id = bpr.start_time_period_id
                and tp_e.time_period_id = bpr.end_time_period_id
                and tp_s.period_set_name = l_calendar
                and tp_e.period_set_name = l_calendar
                and p_effective_date between tp_s.start_date and tp_e.end_date;

Line: 2140

      select budget_id, budget_unit1_id, budget_unit2_id, budget_unit3_id ,period_set_name
      into l_budget_id, l_budget_unit1_id, l_budget_unit2_id, l_budget_unit3_id, l_calendar
      from pqh_budgets
      where position_control_flag = 'Y'
      and budgeted_entity_cd = 'POSITION'
      and business_group_id = l_business_group_id
      and p_effective_date between budget_start_date and budget_end_date
      and (
          hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit1_id) = 'FTE'
          or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit2_id) = 'FTE'
          or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = 'FTE'
      );

Line: 2210

select stp.start_date
  from pqh_budget_periods bper,
       pqh_budget_details bdet,
       per_time_periods stp,
       per_time_periods etp
 where bper.budget_detail_id = bdet.budget_detail_id
   and p_position_id = bdet.position_id
   and bper.start_time_period_id = stp.time_period_id
   and bper.end_time_period_id = etp.time_period_id
   and etp.end_date >= p_start_date
   and stp.start_date <= p_end_date
union
select effective_start_date start_date
  from per_all_assignments_f
 where p_position_id = position_id
   and assignment_type in ('E', 'C')
   and effective_start_date between p_start_date and p_end_date
union
select abv.effective_start_date start_date
  from per_assignment_budget_values_f abv, per_all_assignments_f asg
 where abv.assignment_id = asg.assignment_id
   and p_position_id = asg.position_id
   and asg.assignment_type in ('E', 'C')
   and abv.unit = 'FTE'
   and abv.effective_start_date between asg.effective_start_date and asg.effective_end_date
   and asg.effective_start_date between p_start_date and p_end_date;

Line: 2279

select stp.start_date
  from pqh_budget_periods bper,
       pqh_budget_details bdet,
       per_time_periods stp,
       per_time_periods etp
 where bper.budget_detail_id = bdet.budget_detail_id
   and p_job_id = bdet.job_id
   and bper.start_time_period_id = stp.time_period_id
   and bper.end_time_period_id = etp.time_period_id
   and etp.end_date >= p_start_date
   and stp.start_date <= p_end_date
union
select effective_start_date start_date
  from per_all_assignments_f
 where p_job_id = job_id
   and assignment_type in ('E', 'C')
   and effective_start_date between p_start_date and p_end_date
union
select abv.effective_start_date start_date
  from per_assignment_budget_values_f abv, per_all_assignments_f asg
 where abv.assignment_id = asg.assignment_id
   and p_job_id = asg.job_id
   and asg.assignment_type in ('E', 'C')
   and abv.unit = 'FTE'
   and abv.effective_start_date between asg.effective_start_date and asg.effective_end_date
   and asg.effective_start_date between p_start_date and p_end_date;

Line: 2349

select stp.start_date
  from pqh_budget_periods bper,
       pqh_budget_details bdet,
       per_time_periods stp,
       per_time_periods etp
 where bper.budget_detail_id = bdet.budget_detail_id
   and p_organization_id = bdet.organization_id
   and bper.start_time_period_id = stp.time_period_id
   and bper.end_time_period_id = etp.time_period_id
   and etp.end_date >= p_start_date
   and stp.start_date <= p_end_date
union
select effective_start_date start_date
  from per_all_assignments_f
 where p_organization_id = organization_id
   and assignment_type in ('E', 'C')
   and effective_start_date between p_start_date and p_end_date
union
select abv.effective_start_date start_date
  from per_assignment_budget_values_f abv, per_all_assignments_f asg
 where abv.assignment_id = asg.assignment_id
   and p_organization_id = asg.organization_id
   and asg.assignment_type in ('E', 'C')
   and abv.unit = 'FTE'
   and abv.effective_start_date between asg.effective_start_date and asg.effective_end_date
   and asg.effective_start_date between p_start_date and p_end_date;

Line: 2420

select stp.start_date
  from pqh_budget_periods bper,
       pqh_budget_details bdet,
       per_time_periods stp,
       per_time_periods etp
 where bper.budget_detail_id = bdet.budget_detail_id
   and p_grade_id = bdet.grade_id
   and bper.start_time_period_id = stp.time_period_id
   and bper.end_time_period_id = etp.time_period_id
   and etp.end_date >= p_start_date
   and stp.start_date <= p_end_date
union
select effective_start_date start_date
  from per_all_assignments_f
 where p_grade_id = grade_id
   and assignment_type in ('E', 'C')
   and effective_start_date between p_start_date and p_end_date
union
select abv.effective_start_date start_date
  from per_assignment_budget_values_f abv, per_all_assignments_f asg
 where abv.assignment_id = asg.assignment_id
   and p_grade_id = asg.grade_id
   and asg.assignment_type in ('E', 'C')
   and abv.unit = 'FTE'
   and abv.effective_start_date between asg.effective_start_date and asg.effective_end_date
   and asg.effective_start_date between p_start_date and p_end_date;

Line: 2561

	        select system_type_cd
		  from per_shared_types
		 where shared_type_id = p_unit_id;

Line: 2566

                select bdet.budget_detail_id
                  from pqh_budget_details bdet,pqh_budget_versions bvr
                 where bvr.budget_id = p_budget_id
                   and hr_general.effective_date between bvr.date_from and nvl(bvr.date_to,hr_general.effective_date)
                   and bdet.budget_version_id = bvr.budget_version_id
		   and nvl(p_organization_id, nvl(bdet.organization_id,  -1)) =
					      nvl(bdet.organization_id,  -1)
		   and nvl(p_job_id,          nvl(bdet.job_id,   -1)) =
					      nvl(bdet.job_id,   -1)
		   and nvl(p_position_id,     nvl(bdet.position_id,      -1)) =
					      nvl(bdet.position_id,      -1)
		   and nvl(p_grade_id,        nvl(bdet.grade_id,         -1)) =
					      nvl(bdet.grade_id,         -1);

Line: 2581

                select bpr.budget_unit1_value, bpr.budget_unit2_value, bpr.budget_unit3_value
                  from pqh_budget_periods bpr, per_time_periods tp_s,
		       per_time_periods tp_e
                 where bpr.budget_detail_id = p_budget_detail_id
                   and tp_s.time_period_id = bpr.start_time_period_id
                   and tp_e.time_period_id = bpr.end_time_period_id
                   and tp_s.period_set_name = l_calendar
                   and tp_e.period_set_name = l_calendar
                   and p_effective_date between tp_s.start_date and tp_e.end_date;

Line: 2593

      select budget_id, budget_unit1_id, budget_unit2_id, budget_unit3_id ,period_set_name
      into l_budget_id, l_budget_unit1_id, l_budget_unit2_id, l_budget_unit3_id, l_calendar
      from pqh_budgets
      where position_control_flag = 'Y'
      and budgeted_entity_cd = p_budget_entity
      and business_group_id = p_business_group_id
      and p_effective_date between budget_start_date and budget_end_date
      and (
          hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit1_id)    = p_unit_of_measure
          or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit2_id) = p_unit_of_measure
          or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = p_unit_of_measure
      );

Line: 2658

select to_number(poei_information6,'99999999.99') fte
from per_position_extra_info
where p_effective_date
  between fnd_date.canonical_to_date(poei_information3)
  and nvl(fnd_date.canonical_to_date(poei_information4),hr_general.end_of_time)
  and position_id = p_position_id
    and information_type= 'PER_RESERVED'
    and poei_information5 = p_person_id;

Line: 2693

select sum(to_number(poei_information6,'99999999.99')) fte
from per_position_extra_info
where p_effective_date
  between fnd_date.canonical_to_date(poei_information3)
  and nvl(fnd_date.canonical_to_date(poei_information4),hr_general.end_of_time)
  and  position_id = p_position_id
    and information_type= 'PER_RESERVED';

Line: 2739

select poei_information5 person_id
from per_position_extra_info
where p_effective_date
  between fnd_date.canonical_to_date(poei_information3)
  and nvl(fnd_date.canonical_to_date(poei_information4),hr_general.end_of_time)
  and position_id = p_position_id
  and poei_information5 is not null
    and information_type= 'PER_RESERVED';

Line: 2764

select nvl(abv.value,0)
from per_assignment_budget_values_f abv, per_all_assignments_f asg,
per_assignment_status_types ast
where asg.assignment_id = p_assignment_id
and abv.assignment_id = asg.assignment_id
and asg.assignment_type in ('E', 'C')
and abv.unit = 'FTE'
and p_effective_date between asg.effective_start_date and asg.effective_end_date
and p_effective_date between abv.effective_start_date and abv.effective_end_date
and asg.assignment_status_type_id = ast.assignment_status_type_id
and ast.per_system_status <> 'TERM_ASSIGN';

Line: 2841

   select business_group_id
   from hr_all_positions_f
   where position_id = p_position_id;

Line: 2845

   cursor c1(p_unit_id number) is select system_type_cd from
per_shared_types where shared_type_id = p_unit_id;

Line: 2848

                select bdt.budget_detail_id, bdt.budget_unit1_value, bdt.budget_unit2_value, bdt.budget_unit3_value
                from  pqh_budget_details bdt,pqh_budget_versions bvr
                where bvr.budget_id = p_budget_id
                and p_effective_date between bvr.date_from and nvl(bvr.date_to,p_effective_date)
                and bdt.budget_version_id = bvr.budget_version_id
                and bdt.position_id = p_position_id;

Line: 2863

      select budget_id, budget_unit1_id, budget_unit2_id, budget_unit3_id ,period_set_name
      into l_budget_id, l_budget_unit1_id, l_budget_unit2_id, l_budget_unit3_id, l_calendar
      from pqh_budgets
      where position_control_flag = 'Y'
      and budgeted_entity_cd = 'POSITION'
      and business_group_id = l_business_group_id
      and p_effective_date between budget_start_date and budget_end_date
      and (
          hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit1_id) = 'MONEY'
          or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit2_id) = 'MONEY'
          or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = 'MONEY'
      );

Line: 2934

   select business_group_id
   from hr_all_positions_f
   where position_id = p_position_id;

Line: 2938

   cursor c1(p_unit_id number) is select system_type_cd from
per_shared_types where shared_type_id = p_unit_id;

Line: 2952

      select budget_id, budget_unit1_id, budget_unit2_id, budget_unit3_id ,period_set_name
      , budget_start_date, budget_end_date
      into l_budget_id, l_budget_unit1_id, l_budget_unit2_id, l_budget_unit3_id, l_calendar
      ,l_start_date, l_end_date
      from pqh_budgets
      where position_control_flag = 'Y'
      and budgeted_entity_cd = 'POSITION'
      and business_group_id = l_business_group_id
      and p_effective_date between budget_start_date and budget_end_date
     and (
          hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit1_id) = 'MONEY'
          or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit2_id) = 'MONEY'
          or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = 'MONEY'
      );

Line: 2970

      select budget_version_id into l_budget_version_id
      from pqh_budget_versions bvr
      where budget_id = l_budget_id
      and p_effective_date between bvr.date_from and nvl(bvr.date_to,p_effective_date);

Line: 3060

   select business_group_id
   from per_all_assignments_f
   where assignment_id = p_assignment_id
   and p_effective_date between effective_start_date and effective_end_date;

Line: 3065

   cursor c1(p_unit_id number) is select system_type_cd from
per_shared_types where shared_type_id = p_unit_id;

Line: 3077

      select budget_id, budget_unit1_id, budget_unit2_id, budget_unit3_id ,period_set_name
      , budget_start_date, budget_end_date
      into l_budget_id, l_budget_unit1_id, l_budget_unit2_id, l_budget_unit3_id, l_calendar
      ,l_start_date, l_end_date
      from pqh_budgets
      where position_control_flag = 'Y'
      and budgeted_entity_cd = 'POSITION'
      and business_group_id = l_business_group_id
      and p_effective_date between budget_start_date and budget_end_date
     and (
          hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit1_id) = 'MONEY'
          or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit2_id) = 'MONEY'
          or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(budget_unit3_id) = 'MONEY'
      );

Line: 3092

      select budget_version_id into l_budget_version_id
      from pqh_budget_versions bvr
      where budget_id = l_budget_id
      and p_effective_date between bvr.date_from and nvl(bvr.date_to,p_effective_date);

Line: 3220

select sum(poei_information6) fte
from per_position_extra_info
where p_effective_date
  between fnd_date.canonical_to_date(poei_information3)
  and nvl(fnd_date.canonical_to_date(poei_information4),hr_general.end_of_time)
  and position_id = p_position_id
    and information_type= 'PER_RESERVED'
    and position_extra_info_id <> l_ex_position_extra_info_id;

Line: 3240

select poei_information6
from per_position_extra_info
where position_extra_info_id = p_position_extra_info_id;

Line: 3265

select sum(nvl(value,0))
from per_all_assignments_f asn,
per_assignment_budget_values_f abv,
per_assignment_status_types ast
where abv.assignment_id = asn.assignment_id
and asn.EFFECTIVE_START_DATE <= p_effective_date
and asn.EFFECTIVE_END_DATE >= p_effective_date
and abv.EFFECTIVE_START_DATE <= p_effective_date
and abv.EFFECTIVE_END_DATE >= p_effective_date
and asn.position_id = p_position_id
and asn.person_id <> l_ex_person_id
and asn.assignment_type in ('E', 'C')
and abv.unit = 'FTE'
and asn.assignment_status_type_id = ast.assignment_status_type_id
and ast.per_system_status <> 'TERM_ASSIGN'
and not exists (
    select null
    from per_position_extra_info
    where information_type= 'PER_RESERVED'
    and position_id = p_position_id
    and position_extra_info_id <> l_ex_position_extra_info_id
    and fnd_date.canonical_to_date(poei_information3) <= p_effective_date
    and nvl(fnd_date.canonical_to_date(poei_information4),hr_general.end_of_time)
                     >= p_effective_date
    and poei_information5 = asn.person_id);

Line: 3302

select fte
from hr_all_positions_f
where position_id = p_position_id
and p_effective_date between effective_start_date and effective_end_date;

Line: 3324

select nvl(sum(nvl(value,1)),0)
from per_assignment_budget_values_f abv, per_all_assignments_f asn,
per_assignment_status_types ast
where abv.assignment_id(+) = asn.assignment_id
and asn.position_id = p_position_id
and asn.person_id = p_person_id
and asn.assignment_id <> nvl(p_ex_assignment_id, -1)
and p_effective_date between asn.effective_start_date and asn.effective_end_date
and p_effective_date between abv.effective_start_date and abv.effective_end_date
and asn.assignment_type in ('E', 'C')
and abv.unit(+) = 'FTE'
and asn.assignment_status_type_id = ast.assignment_status_type_id
and ast.per_system_status <> 'TERM_ASSIGN';

Line: 3365

select abv.effective_start_date effective_date
from per_assignment_budget_values_f abv, per_all_assignments_f asn
where abv.assignment_id = asn.assignment_id
and asn.position_id = p_position_id
and abv.effective_start_date between asn.effective_start_date and asn.effective_end_date
and abv.effective_start_date between
  p_start_date and nvl(p_end_date, hr_general.end_of_time)
and asn.effective_start_date between
  p_start_date and nvl(p_end_date, hr_general.end_of_time)
and asn.assignment_type in ('E', 'C')
and abv.unit(+) = 'FTE'
union
select effective_start_date effective_date
from per_all_assignments_f asg
where position_id = p_position_id
and asg.effective_start_date between
p_start_date and nvl(p_end_date, hr_general.end_of_time)
union
select effective_start_date effective_date
from hr_all_positions_f psf
where position_id = p_position_id
and psf.effective_start_date between
p_start_date and nvl(p_end_date, hr_general.end_of_time)
union
select p_start_date effective_date
from dual
union
select nvl(p_end_date, hr_general.end_of_time) effective_date
from dual;

Line: 3396

select business_group_id
from hr_all_positions_f
where position_id = p_position_id;

Line: 3474

select position_type, overlap_period, business_group_id
from hr_all_positions_f
where position_id = p_position_id
and p_effective_date between effective_start_date and effective_end_date;

Line: 3479

/*** index hint added in the select statement of the inner
     query of the cursor as a fix of bug 5963148 **/
cursor c_pos_reserved(p_position_id number, p_effective_date date) is
select 'x'
from (select /*+ INDEX(PER_POSITION_EXTRA_INFO PER_POSITION_EXTRA_INFO_N3)*/
       fnd_date.canonical_to_date(poei_information3) poei_information3,
       nvl(fnd_date.canonical_to_date(poei_information4),
               hr_general.end_of_time) poei_information4
      from per_position_extra_info
      where information_type = 'PER_RESERVED'
       and position_id = p_position_id)
where p_effective_date between poei_information3 and poei_information4;

Line: 3552

/*** index hint added in the select statement of the inner
     query of the cursor as a fix of bug 6409206 **/
cursor c_pos_reserved(p_position_id number,
            p_effective_date date) is
select 'x'
from (select /*+ INDEX(PER_POSITION_EXTRA_INFO PER_POSITION_EXTRA_INFO_N3)*/
       fnd_date.canonical_to_date(poei_information3) poei_information3,
       nvl(fnd_date.canonical_to_date(poei_information4),
               hr_general.end_of_time) poei_information4
      from per_position_extra_info
      where information_type = 'PER_RESERVED'
       and position_id = p_position_id)
where p_effective_date <= poei_information4;

Line: 3588

select effective_start_date
from per_all_assignments_f
where position_id = p_position_id
and assignment_type in ('E', 'C')
and effective_start_date between p_validation_start_date and p_validation_end_date
union
select abv.effective_start_date effective_start_date
from per_assignment_budget_values_f abv, per_all_assignments_f asg
where abv.assignment_id = asg.assignment_id
and asg.position_id = p_position_id
and asg.assignment_type in ('E', 'C')
and abv.effective_start_date between asg.effective_start_date and asg.effective_end_date
and asg.effective_start_date between p_validation_start_date and p_validation_end_date
and asg.business_group_id = abv.business_group_id
union
select effective_start_date
from (select fnd_date.canonical_to_date(poei_information3) effective_start_date
from per_position_extra_info
where position_id = p_position_id
and information_type = 'PER_RESERVED')
where effective_start_date >= p_validation_start_date
union
select effective_start_date
from per_all_assignments_f
where assignment_id = p_assignment_id
and assignment_type in ('E', 'C')
and effective_start_date between p_validation_start_date and p_validation_end_date
union
select abv.effective_start_date effective_start_date
from per_assignment_budget_values_f abv, per_all_assignments_f asg
where abv.assignment_id = asg.assignment_id
and asg.assignment_id = p_assignment_id
and asg.assignment_type in ('E', 'C')
and abv.effective_start_date between asg.effective_start_date and asg.effective_end_date
and asg.effective_start_date between p_validation_start_date and p_validation_end_date
union
select effective_start_date
from hr_all_positions_f
where position_id = p_position_id
and effective_start_date between p_validation_start_date and p_validation_end_date;

Line: 3630

select effective_start_date
from per_all_assignments_f
where position_id = p_position_id
and assignment_type in ('E', 'C')
and effective_start_date between p_validation_start_date and p_validation_end_date
union
select abv.effective_start_date effective_start_date
from per_assignment_budget_values_f abv, per_all_assignments_f asg
where abv.assignment_id = asg.assignment_id
and asg.position_id = p_position_id
and asg.assignment_type in ('E', 'C')
and abv.effective_start_date between asg.effective_start_date and asg.effective_end_date
and asg.effective_start_date between p_validation_start_date and p_validation_end_date
union
select effective_start_date
from (select fnd_date.canonical_to_date(poei_information3) effective_start_date
from per_position_extra_info
where position_id = p_position_id
and information_type = 'PER_RESERVED')
where effective_start_date >= p_validation_start_date
union
select effective_start_date
from hr_all_positions_f
where position_id = p_position_id
and effective_start_date between p_validation_start_date and p_validation_end_date;

Line: 3712

select position_type, fte, organization_id, overlap_period, business_group_id
from hr_all_positions_f
where position_id = p_position_id
and p_effective_date between effective_start_date and effective_end_date;

Line: 3815

select effective_start_date
from per_all_assignments_f
where position_id = p_position_id
and assignment_type in ('E', 'C')
and effective_start_date between p_validation_start_date and p_validation_end_date
union
select abv.effective_start_date effective_start_date
from per_assignment_budget_values_f abv, per_all_assignments_f asg
where abv.assignment_id = asg.assignment_id
and asg.position_id = p_position_id
and asg.assignment_type in ('E', 'C')
and abv.effective_start_date between asg.effective_start_date and asg.effective_end_date
and asg.effective_start_date between p_validation_start_date and p_validation_end_date
and asg.business_group_id = abv.business_group_id
union
select effective_start_date
from per_all_assignments_f
where assignment_id = p_assignment_id
and assignment_type in ('E', 'C')
and effective_start_date between p_validation_start_date and p_validation_end_date
union
select abv.effective_start_date effective_start_date
from per_assignment_budget_values_f abv, per_all_assignments_f asg
where abv.assignment_id = asg.assignment_id
and asg.assignment_id = p_assignment_id
and asg.assignment_type in ('E', 'C')
and abv.effective_start_date between asg.effective_start_date and asg.effective_end_date
and asg.effective_start_date between p_validation_start_date and p_validation_end_date
union
select effective_start_date
from hr_all_positions_f
where position_id = p_position_id
and effective_start_date between p_validation_start_date and p_validation_end_date;

Line: 3850

select effective_start_date
from per_all_assignments_f
where position_id = p_position_id
and assignment_type in ('E', 'C')
and effective_start_date between p_validation_start_date and p_validation_end_date
union
select abv.effective_start_date effective_start_date
from per_assignment_budget_values_f abv, per_all_assignments_f asg
where abv.assignment_id = asg.assignment_id
and asg.position_id = p_position_id
and asg.assignment_type in ('E', 'C')
and abv.effective_start_date between asg.effective_start_date and asg.effective_end_date
and asg.effective_start_date between p_validation_start_date and p_validation_end_date
and asg.business_group_id = abv.business_group_id
union
select effective_start_date
from hr_all_positions_f
where position_id = p_position_id
and effective_start_date between p_validation_start_date and p_validation_end_date;

Line: 4064

select fte, business_group_id
from hr_all_positions_f
where position_id = p_position_id
and p_effective_date
  between effective_start_date and effective_end_date;

Line: 4106

select sum(to_number(poei_information6,'99999999.99'))
from per_position_extra_info
where information_type = 'PER_RESERVED'
and position_id = p_position_id
and p_effective_start_date
  between fnd_date.canonical_to_date(poei_information3)
  and nvl(fnd_date.canonical_to_date(poei_information4),hr_general.end_of_time)
and poei_information5 is null;

Line: 4117

select sum(value)
from per_assignment_budget_values_f abv, per_all_assignments_f asn,
per_assignment_status_types ast
where abv.assignment_id(+) = asn.assignment_id
and p_effective_start_date between asn.effective_start_date and asn.effective_end_date
and p_effective_start_date between abv.effective_start_date and abv.effective_end_date
and asn.position_id = p_position_id
and asn.assignment_type in ('E', 'C')
and abv.unit(+) = 'FTE'
and asn.assignment_status_type_id = ast.assignment_status_type_id
and ast.per_system_status <> 'TERM_ASSIGN'
and not exists
( select null
from per_position_extra_info poei
where poei.information_type = 'PER_RESERVED'
and poei.position_id = p_position_id
and p_effective_start_date
  between fnd_date.canonical_to_date(poei.poei_information3)
  and nvl(fnd_date.canonical_to_date(poei.poei_information4),hr_general.end_of_time)
and to_number(poei.poei_information5) = asn.person_id);

Line: 4190

    select a.organization_id_parent organization_id
    from per_org_structure_elements a
    where a.org_structure_version_id = p_pc_version
    and not exists
    (select null
     from per_org_structure_elements b
     where  b.org_structure_version_id = p_pc_version
         AND b.organization_id_child = a.organization_id_parent);

Line: 4213

 SELECT org_structure_version_id
 FROM per_organization_structures pos,
      per_org_structure_versions ver
 WHERE  pos.organization_structure_id = ver.organization_structure_id
   AND  p_effective_date BETWEEN ver.date_from AND NVL(ver.date_to, p_effective_date)
   AND NVL(pos.position_control_structure_flg,'N') = 'Y'
   AND  pos.business_group_id =  p_business_group_id;

Line: 4249

select position_id, organization_id
from per_all_assignments_f
where assignment_id = p_assignment_id
and p_effective_date between effective_start_date and effective_end_date;

Line: 4292

select bgt.budget_id, bgt.budget_name, bgt.business_group_id,
       bver.budget_version_id,
       bgt.budget_start_date, bgt.budget_end_date, bgt.currency_code
from pqh_budgets bgt,
     pqh_budget_versions bver,
     pqh_budget_details  bdet
where bgt.budget_id = bver.budget_id
and   bver.budget_version_id = bdet.budget_version_id
and   bdet.position_id = p_position_id
and   p_effective_date between bgt.budget_start_date and bgt.budget_end_date
and   p_effective_date between bver.date_from and bver.date_to
and   bgt.budgeted_entity_cd = 'POSITION'
and   bgt.position_control_flag = 'Y'
and   (hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(bgt.budget_unit1_id) = p_budget_unit_cd
       or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(bgt.budget_unit2_id) = p_budget_unit_cd
       or hr_psf_shd.SYSTEM_AVAILABILITY_STATUS(bgt.budget_unit3_id) = p_budget_unit_cd);

Line: 4325

select 'x'
from dual
where exists (
         select null
         from per_pay_proposals
         where assignment_id = p_assignment_id
         and p_effective_date >= change_date
         and approved = 'Y'
     );

Line: 4354

select piv.element_type_id, ppb.input_value_id
from per_all_assignments_f asg,
     per_pay_bases ppb,
     pay_input_values_f piv
where assignment_id = p_assignment_id
and asg.pay_basis_id = ppb.pay_basis_id
and ppb.input_value_id = piv.input_value_id
and p_effective_date between asg.effective_start_date and asg.effective_end_date
and p_effective_date between piv.effective_start_date and piv.effective_end_date;

Line: 4374

      select pettl1.element_name
        from pay_element_types_f_tl pettl1
       where pettl1.element_type_id    = p_element_type_id
         and pettl1.language           = userenv('LANG');

Line: 4389

      select pivtl.name
        from pay_input_values_f_tl pivtl
       where pivtl.input_value_id    = p_input_value_id
         and pivtl.language          = userenv('LANG');

Line: 4409

    select 'X'
    into l_check
    from pqh_bdgt_cmmtmnt_elmnts
    where budget_id = p_budget_id
    and element_type_id = p_element_type_id
    and actual_commitment_type in ('COMMITMENT','BOTH');