APPS.GHR_VALIDATE_PERWSDPO SQL Statements

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

Line: 9

	procedure update_posn_status (p_position_id    in number,
                                      p_effective_date in date ) IS
         l_ovn        number;

Line: 18

                update hr_all_positions_f pos
                set status = 'VALID'
                where pos.position_id = p_position_id
                and p_effective_date between pos.effective_STart_date and pos.effective_end_date
                and pos.status <> 'VALID';

Line: 24

	END update_posn_status ;

Line: 61

	select poei.poei_information3,poei.poei_information4,poei.poei_information5
	  from per_position_extra_info poei
	where poei.position_id=p_position_id and poei.information_type='GHR_US_POSITION_DESCRIPTION';

Line: 66

	select poei.poei_information3,poei.poei_information7
				,poei.poei_information8,poei.poei_information10
				,poei.poei_information16,poei.poei_information13
				,poei.poei_information9,poei.poei_information12
	from per_position_extra_info poei
	where poei.position_id=p_position_id
				and poei.information_type='GHR_US_POS_GRP1';

Line: 75

	select poei.poei_information3
	from per_position_extra_info poei
	where poei.position_id=p_position_id and poei.information_type='GHR_US_POS_GRP2';

Line: 80

	select poei.poei_information3,poei.poei_information5,poei.poei_information6
	from per_position_extra_info poei
	where poei.position_id=p_position_id and poei.information_type='GHR_US_POS_VALID_GRADE';

Line: 85

	select pos.job_id, pos.business_group_id
	from hr_all_positions_f pos
	where pos.position_id= p_position_id
        and   p_effective_date between pos.effective_start_date and pos.effective_end_date;

Line: 91

	select pd.flsa, pd.position_status, pd.position_is,pd.position_sensitivity,pd.competitive_level,
		 cl.official_title, cl.pay_plan, cl.grade_level, cl.occupational_code
	from ghr_position_descriptions pd, ghr_pd_classifications cl
	where pd.position_description_id = l_pos_desc_id
		and pd.position_description_id=cl.position_description_id;

Line: 283

select gpr.pa_request_id
from GHR_PA_REQUESTS gpr
where gpr.to_position_id = p_position_id;

Line: 315

select gpr.pa_request_id
from GHR_PA_REQUESTS gpr
where gpr.person_id = p_person_id;

Line: 344

     select count(*) into l_count from PER_ASSIGNMENTS_F assign
     where assign.location_id=p_location_id  and assign.primary_flag='Y'
       and assign.assignment_type <> 'B';

Line: 363

     select count(*) into l_count from PER_ASSIGNMENTS_F assign
     where assign.position_id=p_position_id and assign.assignment_type <> 'B';

Line: 381

  select 1
  from   PER_ASSIGNMENTS_F asg
  where  asg.position_id = p_position_id
  and    asg.assignment_type <> 'B'
  and    NVL(p_date,trunc(sysdate))
     between asg.effective_start_date and asg.effective_end_date;

Line: 404

  select 1
  from   PER_ASSIGNMENTS_F asg
  where  asg.position_id = p_position_id
  and asg.assignment_id <> p_assignment_id  -- Exclude Current Assignment
  and asg.assignment_type <> 'B'
  and NVL(p_date,trunc(sysdate)) between asg.effective_start_date
                                 and asg.effective_end_date;

Line: 428

  select 1
  from   PER_ASSIGNMENTS_F asg
  where  asg.position_id = p_position_id
  and asg.assignment_type = 'C'
  and NVL(p_date,trunc(sysdate)) between asg.effective_start_date
                                 and asg.effective_end_date;

Line: 466

  SELECT 'Request Number:'||par.request_number||
        ', 1st NOA Code:'||par.first_noa_code||
        DECODE(par.second_noa_code,NULL,NULL, ', 2nd NOA Code:'||par.second_noa_code)||
        ', Effective Date:'||par.effective_date||
        ', Employee_Name:'||per.full_name||
        ', SSN:'||per.national_identifier||
        ', Updater:'||prh.user_name     list_info
  FROM   per_people_f           per
        ,ghr_pa_routing_history prh
        ,ghr_pa_requests        par
  WHERE  par.to_position_id      = p_position_id
  AND    par.effective_date >= p_effective_date
  AND    prh.pa_request_id  = par.pa_request_id
  AND    prh.pa_routing_history_id = (SELECT MAX(prh2.pa_routing_history_id)
                                      FROM   ghr_pa_routing_history prh2
                                      WHERE  prh2.pa_request_id = par.pa_request_id)
  AND    prh.action_taken IN ('FUTURE_ACTION')
  AND    par.person_id = per.person_id
  AND    par.effective_date between per.effective_start_date and per.effective_end_date
  ORDER BY par.effective_date, par.pa_request_id;

Line: 535

	SELECT 'X' value
	FROM   ghr_pa_history
	WHERE  table_name = ghr_history_api.g_eleent_table
--	AND    information1 = p_element_entry_id
    --  Bug #5746242  vmididho  modified the above statement for performance
        AND    information1 = to_char(p_element_entry_id)
	AND    pa_request_id is not null;

Line: 568

  select 1
  from   PER_ASSIGNMENTS_F asg
  where  asg.position_id = p_position_id
  and    asg.assignment_type <> 'B'
  and    asg.effective_start_date >= NVL(p_date,trunc(sysdate));

Line: 591

  select 1
  from   GHR_POSITIONS_H_V phv
  where  phv.position_id = p_position_id
  and    phv.pa_request_id IS NOT NULL
  and    phv.availability_status_id = 1
  and    phv.effective_start_date = p_effective_end_date+1;

Line: 618

  select 1
  from   GHR_POSITIONS_H_V phv
  where  phv.position_id = p_position_id
  and    phv.pa_request_id IS NOT NULL
  and    phv.availability_status_id = 1
  and    phv.effective_start_date >= p_effective_end_date+1;

Line: 645

   SELECT date_effective
   FROM   per_all_positions pap
   WHERE  pap.position_id = p_position_id;