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: 387

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

Line: 409

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

Line: 416

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: 439

select *
from hr_all_positions
where position_id = p_rec.position_id;

Line: 444

select *
from per_all_assignments
where assignment_id = p_rec.assignment_id;

Line: 449

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

Line: 515

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

Line: 625

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: 645

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: 675

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: 681

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: 687

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

Line: 702

  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: 763

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

Line: 771

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: 795

select paf.position_id, paf.person_id, paf.business_group_id, paf.assignment_type  -- bug 7008697
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: 801

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: 811

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')
and asg.assignment_type in ('E','C'); -- bug 7008697

Line: 895

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

Line: 897

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

Line: 927

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: 957

select position_id, person_id, business_group_id, assignment_type
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: 963

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: 973

select sum(value)
from per_assignment_budget_values_f abv, per_all_assignments_f asg,
per_assignment_status_types ast
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)
and asg.assignment_status_type_id = ast.assignment_status_type_id
and ast.per_system_status <> 'TERM_ASSIGN'				-- Condition added for Bug 8309035
and asg.assignment_type in ('E','C'); -- bug 7008697

Line: 1071

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

Line: 1073

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

Line: 1135

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: 1177

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: 1217

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: 1236

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: 1284

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: 1307

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: 1345

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

Line: 1393

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: 1470

select overlap_period, overlap_unit_cd
from hr_all_positions
where position_id = p_position_id;

Line: 1475

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: 1513

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: 1588

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: 1594

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: 1658

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: 1684

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: 1738

/*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)
      -- Start changes for bug 10220040
      and p_assignment_end_date
      between fnd_date.canonical_to_date(poei_information3)
      and fnd_date.canonical_to_date(poei_information4)
      -- End changes for bug 10220040
      and position_id = p_position_id
      and information_type = 'PER_OVERLAP';*/

Line: 1750

select
  fnd_date.canonical_to_date(poei_information3) effective_start_date,
  fnd_date.canonical_to_date(poei_information4) effective_end_date
 from (
  select poei_information3, poei_information4
  from per_position_extra_info
  where information_type = 'PER_OVERLAP'
   and position_id = p_position_id)
 where p_assignment_start_date
  between fnd_date.canonical_to_date(poei_information3)
  and nvl(fnd_date.canonical_to_date(poei_information4), to_date('31/12/4712','dd/mm/rrrr'))
 order by 1,2 desc;

Line: 1764

 select position_type
 from hr_all_positions_f
 where position_id = p_position_id;

Line: 1769

 select
  paaf.assignment_number,
  paaf.effective_start_date,
  paaf.effective_end_date
 from per_all_assignments_f paaf, per_assignment_status_types past
 where paaf.assignment_status_type_id = past.assignment_status_type_id
   and paaf.assignment_type in ('E','C')   -- Added For Bug#14173834
  --and paaf.assignment_type = 'E'         -- Commented For Bug#14173834
  and past.per_system_status <> 'TERM_ASSIGN'
  and paaf.position_id = p_position_id
  and paaf.effective_end_date > lp_effective_end_date
  and paaf.assignment_id <> nvl(p_assignment_id,-1);

Line: 1786

    SELECT ASSIGNMENT_TYPE
    FROM   per_all_assignments_f
    WHERE  assignment_id = p_assignment_id
    AND    p_assignment_start_date between effective_start_date and effective_end_date;

Line: 1911

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

Line: 1951

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

Line: 1969

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

Line: 1988

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

Line: 2006

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

Line: 2024

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

Line: 2061

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

Line: 2079

select count(1)
from per_all_assignments_f paaf, per_assignment_status_types past
where paaf.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status <> 'TERM_ASSIGN'
and paaf.position_id = p_position_id
and paaf.assignment_type in  ('E', 'C')
and p_effective_date between paaf.effective_start_date and paaf.effective_end_date;

Line: 2097

select max_persons
from hr_all_positions
where position_id = p_position_id;

Line: 2110

select proposed_date_for_layoff
from hr_all_positions_f
where position_id = p_position_id;

Line: 2123

select fte/max_persons
from hr_all_positions
where position_id = p_position_id;

Line: 2136

select position_type
from hr_all_positions
where position_id = p_position_id;

Line: 2149

select entry_grade_id
from hr_all_positions
where position_id = p_position_id;

Line: 2162

select work_period_type_cd
from hr_all_positions
where position_id = p_position_id;

Line: 2199

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

Line: 2222

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

Line: 2236

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

Line: 2271

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

Line: 2302

   select business_group_id
   from hr_all_positions_f
   where position_id = p_position_id;

Line: 2306

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

Line: 2308

   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: 2315

                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: 2333

      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: 2403

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: 2472

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: 2542

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: 2613

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: 2754

	        select system_type_cd
		  from per_shared_types
		 where shared_type_id = p_unit_id;

Line: 2759

                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: 2774

                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: 2786

      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: 2854

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: 2865

select to_number(pos.poei_information6,'99999999.99') fte
from
  (SELECT poei_information6, poei_information3, poei_information4, ROWNUM rn from per_position_extra_info
	WHERE position_id = p_position_id
	and information_type= 'PER_RESERVED'
	and poei_information5 = p_person_id) pos
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);

Line: 2907

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: 2916

select sum(to_number(poei_information6,'99999999.99')) fte
from ( SELECT poei_information6,poei_information3,poei_information4, ROWNUM rn from per_position_extra_info
	WHERE  position_id = p_position_id
	and information_type= 'PER_RESERVED') pos
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);

Line: 2970

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: 2980

select poei_information5 person_id
from ( SELECT poei_information5, poei_information3, poei_information4, ROWNUM rn
	FROM per_position_extra_info
	WHERE position_id = p_position_id
	and poei_information5 is not null
	and information_type= 'PER_RESERVED') pos
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);

Line: 3012

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: 3089

   select business_group_id
   from hr_all_positions_f
   where position_id = p_position_id;

Line: 3093

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

Line: 3096

                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: 3111

      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: 3182

   select business_group_id
   from hr_all_positions_f
   where position_id = p_position_id;

Line: 3186

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

Line: 3200

      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: 3218

      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: 3308

   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: 3313

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

Line: 3325

      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: 3340

      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: 3471

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: 3481

select sum(poei_information6) fte
from (SELECT poei_information6,poei_information3, poei_information4, ROWNUM rn
	FROM per_position_extra_info
	WHERE position_id = p_position_id
	and information_type= 'PER_RESERVED'
	and position_extra_info_id <> l_ex_position_extra_info_id) pei
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);

Line: 3506

select poei_information6
from per_position_extra_info
where position_extra_info_id = p_position_extra_info_id;

Line: 3531

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 (SELECT position_extra_info_id,poei_information3,poei_information4,poei_information5, ROWNUM rn           -- changed for 8727415
	  FROM  per_position_extra_info
	  where information_type= 'PER_RESERVED'
	  and position_id = p_position_id ) pei
    where 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: 3570

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: 3592

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: 3633

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: 3664

select business_group_id
from hr_all_positions_f
where position_id = p_position_id;

Line: 3768

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: 3773

/*** index hint added in the select statement of the inner
     query of the cursor as a fix of bug 5963148 **/

-- Start changes for bug 10262123
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)*/
       poei_information3,
       poei_information4
      from per_position_extra_info
      where information_type = 'PER_RESERVED'
       and position_id = p_position_id)
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);

Line: 3859

/*** 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: 3897

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: 3939

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: 4060

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: 4167

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
and effective_end_date between p_validation_start_date and p_validation_end_date  -- 11059779
union
--11059779
select effective_end_date --effective_start_date changed for bug 12751673
from per_all_assignments_f
where position_id = p_position_id
and assignment_type in ('E', 'C')
and( p_validation_start_date between effective_start_date and effective_end_date
or p_validation_end_date between  effective_start_date and effective_end_date)
--11059779
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: 4212

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: 4442

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: 4484

select sum(to_number(poei_information6,'99999999.99'))
from (SELECT poei_information6,poei_information3, poei_information4,poei_information5, ROWNUM rn		-- changed for 8727415
	FROM  per_position_extra_info
	where information_type = 'PER_RESERVED'
	and position_id = p_position_id ) poei
Where 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: 4496

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 (SELECT poei_information3, poei_information4, poei_information5, ROWNUM rn				-- changed for 8727415
	FROM per_position_extra_info
	where information_type = 'PER_RESERVED'
	and position_id = p_position_id) poei
where 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: 4578

    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: 4601

 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: 4637

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: 4680

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: 4713

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: 4742

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: 4762

      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: 4777

      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: 4797

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