eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.GHR_VALIDATE_CHECK SQL Statements

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

Line: 110


	select pps.status from hr_all_positions_f pps  -- Venkat - Position DT
	where pps.position_id = p_position_id
        and p_pa_request_rec.effective_date between pps.effective_start_date
               and pps.effective_end_date;

Line: 118


	select ppt.system_person_type, ppt.user_person_type  from PER_PERSON_TYPES ppt, PER_PEOPLE_F ppf
	where ppf.person_id = p_person_id
	and trunc(p_eff_date) between ppf.effective_start_date and ppf.effective_end_date
	and   ppt.person_type_id = ppf.person_type_id;

Line: 126


   select  asg1.assignment_id
   from    per_assignments_f asg1
   where
   asg1.person_id            <> p_person_id
   and     asg1.position_id   = p_to_position_id
   and     asg1.assignment_type NOT IN ('B','A')
   and     (asg1.effective_start_date >= p_eff_date
            or p_eff_date
            between asg1.effective_start_date and asg1.effective_end_date);

Line: 137


   select  asg1.assignment_id
   from    per_assignments_f asg1
   where   asg1.person_id            <> p_person_id
   and     asg1.position_id   = p_to_position_id
   and     asg1.assignment_type NOT IN ('B','A')
   and     (p_eff_date
	    between asg1.effective_start_date and asg1.effective_end_date);

Line: 149


	Select per_system_status from per_assignment_status_types pst, per_assignments_f paf
	where paf.assignment_status_type_id = pst.assignment_status_type_id
        and   paf.assignment_type <> 'B'
	and   paf.assignment_id = p_asg_id
	and   pst.per_system_status = p_status
	and trunc(p_eff_date) between paf.effective_start_date and paf.effective_end_date;

Line: 160


    select  1
    from   ghr_noac_las       nla
    where   nla.nature_of_action_id  = p_pa_request_rec.first_noa_id
    and     SUBSTR(nla.lac_lookup_code,1,3) = p_pa_request_rec.first_action_la_code1
    and     nla.valid_first_lac_flag = 'Y'
    and     nvl(p_pa_request_rec.effective_date,trunc(sysdate))
    between nvl(nla.date_from,nvl(p_pa_request_rec.effective_date,trunc(sysdate)))
    and     nvl(nla.date_to,nvl(p_pa_request_rec.effective_date,trunc(sysdate)));

Line: 170


    select  1
    from   ghr_noac_las       nla
    where   nla.nature_of_action_id  = p_pa_request_rec.first_noa_id
    and     SUBSTR(nla.lac_lookup_code,1,3) = p_pa_request_rec.first_action_la_code2
    and     nla.valid_second_lac_flag = 'Y'
    and     nvl(p_pa_request_rec.effective_date,trunc(sysdate))
    between nvl(nla.date_from,nvl(p_pa_request_rec.effective_date,trunc(sysdate)))
    and     nvl(nla.date_to,nvl(p_pa_request_rec.effective_date,trunc(sysdate)));

Line: 180


    select  1
    from   ghr_noac_las       nla
    where   nla.nature_of_action_id  = p_pa_request_rec.second_noa_id
    and     SUBSTR(nla.lac_lookup_code,1,3)  = p_pa_request_rec.second_action_la_code1
    and     nla.valid_first_lac_flag = 'Y'
    and     nvl(p_pa_request_rec.effective_date,trunc(sysdate))
    between nvl(nla.date_from,nvl(p_pa_request_rec.effective_date,trunc(sysdate)))
    and     nvl(nla.date_to,nvl(p_pa_request_rec.effective_date,trunc(sysdate)));

Line: 190


    select  1
    from   ghr_noac_las       nla
    where   nla.nature_of_action_id  = p_pa_request_rec.second_noa_id
    and     SUBSTR(nla.lac_lookup_code,1,3) = p_pa_request_rec.second_action_la_code2
    and     nla.valid_second_lac_flag = 'Y'
    and     nvl(p_pa_request_rec.effective_date,trunc(sysdate))
    between nvl(nla.date_from,nvl(p_pa_request_rec.effective_date,trunc(sysdate)))
    and     nvl(nla.date_to,nvl(p_pa_request_rec.effective_date,trunc(sysdate)));

Line: 200


SELECT  rei_information3 temp_step
FROM    ghr_pa_request_extra_info
WHERE   pa_request_id = p_pa_request_rec.pa_request_id
AND     information_type = 'GHR_US_PAR_RG_TEMP_PROMO';

Line: 210


   Select fam.noa_family_code
   from   ghr_noa_families    nfa,
   ghr_families               fam
   where  nfa.nature_of_action_id  = p_pa_request_rec.first_noa_id
   and    nfa.noa_family_code      = fam.noa_family_code
   and    fam.update_hr_flag       = 'Y'
   and    l_effective_date between NVL(nfa.start_date_active,l_effective_date)
                               and NVL(nfa.end_date_active,l_effective_date);

Line: 222


        select eev.screen_entry_value
          from pay_element_types_f elt,
               pay_input_values_f ipv,
               pay_element_entries_f ele,
               pay_element_entry_values_f eev
         where trunc(p_pa_request_rec.effective_date) between elt.effective_start_date
                                   and elt.effective_end_date
           and trunc(p_pa_request_rec.effective_date) between ipv.effective_start_date
                                   and ipv.effective_end_date
           and trunc(p_pa_request_rec.effective_date) between ele.effective_start_date
                                   and ele.effective_end_date
           and trunc(p_pa_request_rec.effective_date) between eev.effective_start_date
                                   and eev.effective_end_date
           and elt.element_type_id = ipv.element_type_id
           and ele.assignment_id = p_pa_request_rec.employee_assignment_id
           and elt.element_name IN ('Supervisory Differential','AUO','Availability Pay')
           and ipv.input_value_id = eev.input_value_id
           and ele.element_entry_id + 0 = eev.element_entry_id ;

Line: 243


    SELECT par.pa_request_id futr_rpa
    FROM ghr_pa_routing_history prh
		,ghr_pa_requests        par
    WHERE prh.pa_request_id  = par.pa_request_id
	AND par.person_id = c_person_id
	AND par.effective_date > c_effective_date
	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','UPDATE_HR_COMPLETE')
	AND    par.NOA_FAMILY_CODE <> 'CANCEL'
	AND (   ( par.second_noa_code IS NULL
        AND NVL(par.first_noa_cancel_or_correct,'X') <> 'CANCEL'
          )
     OR  (  par.second_noa_code IS NOT NULL
        AND  par.NOA_FAMILY_CODE <> 'CORRECT'
        AND ( NVL(par.first_noa_cancel_or_correct,'X') <> 'CANCEL'
          OR NVL(par.second_noa_cancel_or_correct,'X') <> 'CANCEL'
            )
         )
     OR  (  par.second_noa_code IS NOT NULL
        AND  par.NOA_FAMILY_CODE = 'CORRECT'
        AND  NVL(par.second_noa_cancel_or_correct,'X') <> 'CANCEL'
         )
       );

Line: 278


  select 1 from pay_user_tables
  where substr(user_table_name,1,4) = 'ESSL'
  and user_table_id = l_user_tab_id;

Line: 288


 SELECT  NVL(rei_information3,0)+NVL(rei_information4,0)+NVL(rei_information5,0)+NVL(rei_information6,0)
	+NVL(rei_information7,0)+NVL(rei_information8,0)+NVL(rei_information9,0)+NVL(rei_information10,0) amount,
	 rei_information12 nte_date,rei_information13 premium_pay_ind

 FROM    ghr_pa_request_extra_info
 WHERE   pa_request_id = p_pa_request_rec.pa_request_id
 AND     information_type = 'GHR_US_PAR_MD_DDS_PAY';

Line: 297


 SELECT  NVL(rei_information3,0) premium_pay_ind
 FROM    ghr_pa_request_extra_info
 WHERE   pa_request_id = p_pa_request_rec.pa_request_id
 AND     information_type = 'GHR_US_PAR_PREMIUM_PAY';

Line: 304


 SELECT  NVL(rei_information3,0) premium_pay_ind
 FROM    ghr_pa_request_extra_info
 WHERE   pa_request_id = p_pa_request_rec.pa_request_id
 AND     information_type = 'GHR_US_PAR_PREMIUM_PAY_IND';

Line: 310


 SELECT from_occ_code
 FROM   ghr_pa_requests
 WHERE  pa_request_id = p_pa_request_rec.pa_request_id;

Line: 319


SELECT  rei_information8 repay_sch,
	rei_information9 review_Date
FROM    ghr_pa_request_extra_info
WHERE   pa_request_id = p_pa_request_rec.pa_request_id
AND     information_type = 'GHR_US_PAR_STUDENT_LOAN';

Line: 332


    select ele.effective_end_date
    from pay_element_types_f   elt,
         pay_element_links_f   ell,
         pay_element_entries_f ele
    where p_pa_request_rec.effective_date between elt.effective_start_date and elt.effective_end_date
      and p_pa_request_rec.effective_date between ell.effective_start_date and ell.effective_end_date
      and p_pa_request_rec.effective_date between ele.effective_start_date and ele.effective_end_date
    and elt.element_type_id = ell.element_type_id
    and ell.element_link_id = ele.element_link_id
    and ele.assignment_id = p_asg_id
    and elt.element_name  = p_ele_name;

Line: 347


SELECT effective_end_date
FROM   pay_element_entries_f
WHERE  element_type_id = (SELECT element_type_id
                          FROM   pay_element_types_f
			  WHERE  element_name = p_ele_name
			  and    p_pa_request_rec.effective_date between effective_start_Date and effective_end_date )
  and  assignment_id  = p_asg_id;

Line: 361


  SELECT gdf.segment1 pay_plan
        ,gdf.segment2 grade_or_level
  FROM  per_grade_definitions gdf
       ,per_grades            grd
  WHERE grd.grade_id = p_grade_id
  AND   grd.grade_definition_id = gdf.grade_definition_id;

Line: 387


       Select business_group_id bg
       from per_assignments_f
       where assignment_id = p_assignment_id
       and   p_eff_date between effective_start_date
             and effective_end_date;

Line: 404


SELECT user_status
FROM   per_assignment_status_types pst, per_assignments_f paf
WHERE  paf.assignment_status_type_id = pst.assignment_status_type_id
AND    paf.assignment_type <> 'B'
AND    paf.assignment_id = p_asg_id
AND   (p_eff_date) BETWEEN paf.effective_start_date AND paf.effective_end_date;

Line: 430


    SELECT pa_incentive_category_end_date,pa_incentive_category_percent
    FROM   ghr_pa_incentives
    WHERE  pa_request_id = p_pa_request_rec.pa_request_id
    AND    pa_incentive_category = 'Biweekly';

Line: 436


    SELECT count(*) cnt
    FROM   ghr_pa_incentives
    WHERE  pa_request_id = p_pa_request_rec.pa_request_id;

Line: 442


    SELECT  count(*) cnt
    FROM    pay_element_entries_f ee, pay_element_types_f et
    WHERE   ee.assignment_id = l_asg_id
      AND   ee.element_type_id = et.element_type_id
      AND   et.element_name like '%Incentive%'
      AND   ee.effective_start_date > l_effective_date;

Line: 450


   SELECT   1
    FROM    ghr_noa_families  noa
    WHERE   noa.nature_of_action_id = l_noa_id
    AND     noa.noa_family_code     = l_noa_fam_code
    AND     l_eff_date BETWEEN nvl(noa.start_date_active,l_eff_date)
                                 AND nvl(noa.end_date_active,l_eff_date)
    AND     noa.enabled_flag        = 'Y';

Line: 469


      SELECT rei_information3 srvc_oblg_type,
             rei_information4 srvc_oblg_st_date,
             rei_information5 srvc_oblg_end_date
        FROM ghr_pa_request_extra_info
       WHERE pa_request_id = p_pa_request_rec.pa_request_id AND
             information_type = 'GHR_US_PAR_SERVICE_OBLIGATION';

Line: 481


/* Do not allow update to HR if Position_Status is Invalid */

	FOR v_get_psn_status IN
		c_get_psn_status(p_pa_request_rec.to_position_id) LOOP
		l_psn_status    :=  v_get_psn_status.status;

Line: 493


            ghr_validate_perwsdpo.update_posn_status(p_pa_request_rec.to_position_id,p_pa_request_rec.effective_date);

Line: 1270


      hr_utility.set_message(8301,'GHR_38693_NO_UPDATE_TO_POS');

Line: 1981


         hr_utility.set_message(8301,'GHR_ONE_OP_UPDATE_ONLY');

Line: 2197


/**** As per GPPA update 46 req. for 890 any employee is fine
    IF NVL(p_pa_request_rec.effective_date,TRUNC(sysdate)) >= to_date('07/01/2007','DD/MM/YYYY') AND
            p_pa_request_rec.first_noa_code = '890' THEN
        IF l_psi = '00' THEN
            hr_utility.set_message(8301, 'GHR_38462_AFHR_POS_SEL');

Line: 2299


  SELECT  eev.screen_entry_value
         ,eev.effective_start_date
  FROM    pay_element_types_f        elt
         ,pay_input_values_f         ipv
         ,pay_element_entries_f      ele
         ,pay_element_entry_values_f eev
  WHERE  p_effective_date <  eev.effective_start_date
  AND    eev.effective_end_date IS NULL
  AND    elt.element_type_id    = ipv.element_type_id
  AND    upper(elt.element_name)= upper(p_element_name)
  AND    ipv.input_value_id     = eev.input_value_id
  AND    ele.assignment_id      = p_assignment_id
  AND    ele.element_entry_id+0 = eev.element_entry_id
  AND    upper(ipv.name )       = upper(p_input_value_name)
--  AND    NVL(elt.business_group_id,0)  = NVL(ipv.business_group_id,0)
  AND    (elt.business_group_id is NULL or elt.business_group_id  = p_bg_id);

Line: 2317


       Select distinct business_group_id bg
       from per_assignments_f
       where assignment_id = p_assignment_id
       and   p_eff_date between effective_start_date
             and effective_end_date;