APPS.GHR_UTILITY SQL Statements

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

Line: 11

       SELECT hoi.org_information_context
            , hoi.org_information1
       FROM hr_organization_information hoi
       WHERE hoi.org_information_context = 'GHR_US_ORG_INFORMATION'
         AND hoi.organization_id = p_current_bg;

Line: 18

       SELECT hoi.org_information_context
            , hoi.org_information6
       FROM hr_organization_information hoi
       WHERE hoi.org_information_context = 'GHR_US_ORG_INFORMATION'
         AND hoi.organization_id = p_current_bg
         AND hoi.org_information6 = 'Y';

Line: 26

       SELECT 1
       FROM ben_pgm_f
       WHERE name = 'Federal Employees Health Benefits' and business_group_id = p_current_bg;

Line: 31

       SELECT 1
       FROM ben_pgm_f
       WHERE name = 'Federal Employees Health Benefits'
       and   business_group_id = p_current_bg
       and   pgm_stat_cd = 'A';

Line: 38

       SELECT 1
       FROM ben_pgm_f
       WHERE name = 'Federal Thrift Savings Plan (TSP)'
       and   business_group_id = p_current_bg
       and   pgm_stat_cd = 'A';

Line: 63

		  select    flx.id_flex_num
		  from      fnd_id_flex_structures_tl flx
		  where     flx.id_flex_code           =  c_flex_code -- 'POS'
		  and       flx.application_id         =  800   --
		  and       flx.id_flex_structure_name =  c_struct_name -- 'US Federal Position'
		  and	    flx.language	       = 'US';

Line: 84

		  select org_information8
		  from hr_organization_information oi
		  where org_information_context = 'Business Group Information'
		  and organization_id = c_bus_org_id;

Line: 106

			SELECT
			  SEGMENT_NAME,
			  DESCRIPTION,
			  ENABLED_FLAG,
			  APPLICATION_COLUMN_NAME,
			  SEGMENT_NUM,
			  DISPLAY_FLAG,
			  APPLICATION_COLUMN_INDEX_FLAG,
			  DEFAULT_VALUE,
			  RUNTIME_PROPERTY_FUNCTION,
			  ADDITIONAL_WHERE_CLAUSE,
			  REQUIRED_FLAG,
			  SECURITY_ENABLED_FLAG,
			  DISPLAY_SIZE,
			  MAXIMUM_DESCRIPTION_LEN,
			  CONCATENATION_DESCRIPTION_LEN,
			  FORM_ABOVE_PROMPT,
			  FORM_LEFT_PROMPT,
			  RANGE_CODE,
			  FLEX_VALUE_SET_ID,
			  DEFAULT_TYPE,
			  ID_FLEX_NUM,
			  ID_FLEX_CODE,
			  APPLICATION_ID,
			  ROW_ID
			FROM
			  FND_ID_FLEX_SEGMENTS_VL
			WHERE
			  (ID_FLEX_NUM= c_flex_num) and -- 50520
			  (ID_FLEX_CODE= c_flex_code) and -- 'POS'
			  (APPLICATION_ID= 800)
			  order by segment_num;

Line: 155

		SELECT concatenated_segment_delimiter delimiter
		FROM fnd_id_flex_structures_vl
		WHERE (APPLICATION_ID=800) AND
		(ID_FLEX_CODE= c_flex_code) AND
		id_flex_num =  c_flex_num; --

Line: 396

	select information6,segment1,segment2,segment3,segment4,
		segment5,segment6,segment7
	from per_position_definitions pdf, hr_all_positions_f pos
	where pos.position_definition_id = pdf.position_definition_id
	and pos.position_id = c_position_id
        and c_effective_date between pos.effective_start_date and
         pos.effective_end_date;

Line: 405

	SELECT 1 FROM hr_positions_f
	WHERE information6 = to_char(c_position_id); -- Bug 4576746

Line: 416

    select trunc(effective_date) session_date
      from fnd_sessions
      where session_id = (select userenv('sessionid') from dual);

Line: 479

PROCEDURE validate_delete_nfc(
P_POSITION_ID in NUMBER
,P_EFFECTIVE_DATE in DATE
 ) IS

	CURSOR c_check_child(c_position_id hr_positions_f.position_id%type) IS
	SELECT 1 FROM hr_positions_f
	WHERE information6 = to_char(c_position_id); -- Bug 4576746

Line: 493

	hr_utility.set_location('Entering: Validate_Delete_NFC ',10);

Line: 500

	hr_utility.set_location(' Validate_Delete_NFC ',60);

Line: 508

END validate_delete_nfc;

Line: 546

	select information6,segment1,segment2,segment3,segment4,
		segment5,segment6,segment7
	from per_position_definitions pdf, hr_all_positions_f pos
	where pos.position_definition_id = pdf.position_definition_id
	and pos.position_id = c_position_id
        and c_effective_date between pos.effective_start_date and
         pos.effective_end_date;

Line: 561

    select trunc(effective_date) session_date
      from fnd_sessions
      where session_id = (select userenv('sessionid') from dual);

Line: 592

PROCEDURE update_nfc_eit(
P_POSITION_ID in NUMBER
,P_SEGMENT1 in VARCHAR2
,P_SEGMENT2 in VARCHAR2
,P_SEGMENT3 in VARCHAR2
,P_SEGMENT4 in VARCHAR2
,P_SEGMENT5 in VARCHAR2
,P_SEGMENT6 in VARCHAR2
,P_SEGMENT7 in VARCHAR2
,P_SEGMENT8 in VARCHAR2
,P_SEGMENT9 in VARCHAR2
,P_SEGMENT10 in VARCHAR2
,P_SEGMENT11 in VARCHAR2
,P_SEGMENT12 in VARCHAR2
,P_SEGMENT13 in VARCHAR2
,P_SEGMENT14 in VARCHAR2
,P_SEGMENT15 in VARCHAR2
,P_SEGMENT16 in VARCHAR2
,P_SEGMENT17 in VARCHAR2
,P_SEGMENT18 in VARCHAR2
,P_SEGMENT19 in VARCHAR2
,P_SEGMENT20 in VARCHAR2
,P_SEGMENT21 in VARCHAR2
,P_SEGMENT22 in VARCHAR2
,P_SEGMENT23 in VARCHAR2
,P_SEGMENT24 in VARCHAR2
,P_SEGMENT25 in VARCHAR2
,P_SEGMENT26 in VARCHAR2
,P_SEGMENT27 in VARCHAR2
,P_SEGMENT28 in VARCHAR2
,P_SEGMENT29 in VARCHAR2
,P_SEGMENT30 in VARCHAR2
,P_INFORMATION6 in VARCHAR2
,P_EFFECTIVE_DATE in DATE
,P_LANGUAGE_CODE in VARCHAR2 ) IS
 Cursor c_pos_ei(p_position_id in NUMBER,
      p_information_type in VARCHAR2)  is
     select position_extra_info_id,
            object_version_number
     from   per_position_extra_info
     where  position_id      = p_position_id
     and    information_type = p_information_type;

Line: 650

	   hr_position_extra_info_api.update_position_extra_info
	  ( p_position_extra_info_id    =>    l_Extra_Info_Id
	  , p_object_version_number     =>    l_ovn
	  , p_poei_information3         =>    p_segment4);

Line: 665

	   hr_position_extra_info_api.update_position_extra_info
	  ( p_position_extra_info_id    =>    l_Extra_Info_Id
	  , p_object_version_number     =>    l_ovn
	  , p_poei_information3         =>    p_segment7);

Line: 680

	   hr_position_extra_info_api.update_position_extra_info
	  ( p_position_extra_info_id    =>    l_Extra_Info_Id
	  , p_object_version_number     =>    l_ovn
	  , p_poei_information21         =>    p_segment3);

Line: 690

END update_nfc_eit;

Line: 738

  select ppf.business_group_id
  from per_all_people_f ppf
  where ppf.person_id = p_person_id
  and trunc(sysdate) between ppf.effective_start_date
  and ppf.effective_end_date;

Line: 744

  select pos.business_group_id
  from hr_all_positions_f pos  -- Venkat -- Position DT
  where pos.position_id = p_position_id
  and trunc(sysdate) between pos.effective_start_date
  and pos.effective_end_date;

Line: 751

  select asg.business_group_id
  from per_all_assignments_f asg
  where asg.assignment_id = p_assignment_id
  and trunc(sysdate) between asg.effective_start_date
  and asg.effective_end_date;

Line: 758

  select pbg.security_group_id
  from per_business_groups pbg
  where pbg.business_group_id =  p_business_group_id;

Line: 844

   cursor c_noa_code is select code from ghr_nature_of_actions
        where nature_of_action_id= p_nature_of_action_id;

Line: 859

select nvl(max(fnd_date.canonical_to_date(rei_information3))+1,p_effective_date)
  authentication_date
  from ghr_pa_requests par, ghr_pa_request_extra_info rei
  where par.person_id in ( select  person_id from ghr_pa_requests where
   pa_request_id = p_pa_request_id )
  and par.effective_date = p_effective_date
  and par.pa_notification_id is not null
  and par.pa_request_id = rei.pa_request_id
  and rei.information_type = 'GHR_US_PAR_NFC_INFO';

Line: 891

select effective_date,first_noa_code,
           second_noa_code,pa_notification_id,pa_request_id
    from ghr_pa_requests
    where pa_notification_id is not null
    and person_id = p_person_id
    and pa_notification_id < p_pa_notification_id
    and effective_date <= p_effective_date
    and first_noa_code not in ('001') 	-- Exclude all cancellations
    and pa_request_id not in ( select altered_pa_request_id
       from ghr_pa_requests where
    pa_notification_id = p_pa_notification_id) -- Excludes original action
    order by pa_notification_id desc;

Line: 922

select effective_date,first_noa_code,
 first_action_la_code1,first_action_la_code2,
 second_action_la_code1,second_action_la_code2
    from ghr_pa_requests
    where pa_notification_id is not null
    and person_id = p_person_id
    and pa_notification_id < p_pa_notification_id
    and effective_date <= p_effective_date
    and first_noa_code not in ('001') 	-- Exclude all cancellations
    and pa_request_id not in ( select altered_pa_request_id
       from ghr_pa_requests where
    pa_notification_id = p_pa_notification_id) -- Excludes original action
    order by pa_notification_id desc;

Line: 959

select 'X'
from per_people_f per, per_person_types ppt, ghr_pa_requests par
where par.pa_request_id = p_pa_request_id
and per.person_id = par.person_id
and ppt.person_type_id = per.person_type_id
and ppt.system_person_type = 'EX_EMP'
and (par.effective_date - 1) between per.effective_start_date
and per.effective_end_date
and par.first_noa_code like '5%';