APPS.HR_AMEUTIL_SS SQL Statements

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

Line: 28

    select t.item_type
    into c_item_type
    from hr_api_transactions t
    where transaction_id=get_item_type.p_transaction_id;

Line: 38

     select ts.item_type
     into get_item_type.c_item_type
     from hr_api_transaction_steps ts
     where ts.transaction_id=get_item_type.p_transaction_id
     and ts.item_type is not null and rownum <=1;

Line: 71

    select t.item_key
    into get_item_key.c_item_key
    from hr_api_transactions t
    where transaction_id=get_item_key.p_transaction_id;

Line: 81

     select ts.item_key
     into get_item_key.c_item_key
     from hr_api_transaction_steps ts
     where ts.transaction_id=get_item_key.p_transaction_id
     and ts.item_type is not null and rownum <=1;

Line: 109

 select t.process_name
    into get_process_name.c_process_name
    from hr_api_transactions t
    where transaction_id=get_process_name.p_transaction_id;

Line: 144

	select decode(hats.api_name,
	'BEN_PROCESS_COMPENSATION_W.PROCESS_API','BENEFITS',
	'HR_APPLY_FOR_JOB_APP_WEB.PROCESS_API','BENEFITS',
	'HR_ASSIGNMENT_COMMON_SAVE_WEB.PROCESS_API',l_category_undefined,
	'HR_BASIC_DETAILS_WEB.PROCESS_API',l_category_undefined,
	'HR_CAED_SS.PROCESS_API',l_category_undefined,
	'HR_CCMGR_SS.PROCESS_API',l_category_undefined,
	'HR_COMP_PROFILE_SS.PROCESS_API',l_category_undefined,
	'HR_COMP_REVIEW_WEB_SS.PROCESS_API',l_category_undefined,
	'HR_EMP_ADDRESS_WEB.PROCESS_API',l_category_undefined,
	'HR_EMP_CONTACT_WEB.PROCESS_API',l_category_undefined,
	'HR_EMP_MARITAL_WEB.PROCESS_API',l_category_undefined,
	'HR_LOA_SS.PROCESS_API',l_category_undefined,
	'HR_PAY_RATE_SS.PROCESS_API','SALARY',
	'HR_PAY_RATE_SS.PROCESS_API_JAVA','SALARY',
 	'PER_SSHR_CHANGE_PAY.PROCESS_API','SALARY',
	'PER_SSHR_CHANGE_PAY.PROCESS_API_JAVA','SALARY',
	'HR_PERCMPTNCE_REVIEW_WEB.PROCESS_API',l_category_undefined,
	'HR_PROCESS_ADDRESS_SS.PROCESS_API',l_category_undefined,
	'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API','ASSIGNMENT',
	'HR_PROCESS_CONTACT_SS.PROCESS_API',l_category_undefined,
	'HR_PROCESS_CONTACT_SS.PROCESS_CREATE_CONTACT_API',l_category_undefined,
	'HR_PROCESS_EIT_SS.PROCESS_API',l_category_undefined,
	'HR_PROCESS_PERSON_SS.PROCESS_API',l_category_undefined,
	'HR_PROCESS_PHONE_NUMBERS_SS.PROCESS_API',l_category_undefined,
	'HR_PROCESS_SIT_SS.PROCESS_API',l_category_undefined,
	'HR_PROF_UTIL_WEB.PROCESS_API',l_category_undefined,
	'HR_QUA_AWARDS_UTIL_SS.PROCESS_API',l_category_undefined,
	'HR_SALARY_WEB.PROCESS_API','SALARY',
	'HR_SALARY_WEB.process_API','SALARY',
	'HR_SIT_WEB.PROCESS_API',l_category_undefined,
	'HR_SUPERVISOR_SS.PROCESS_API','TRANSFER',
	'HR_SUPERVISOR_WEB.PROCESS_API','TRANSFER',
	'HR_SUPERVISOR_WEB.process_API','TRANSFER',
	'HR_TERMINATION_SS.PROCESS_API','TERMINATION',
	'HR_TERMINATION_SS.PROCESS_SAVE','TERMINATION',
	'HR_TERMINATION_WEB.PROCESS_API','TERMINATION',
	'PAY_PPMV4_SS.PROCESS_API','PAYROLL',
	'PAY_US_OTF_UTIL_WEB.UPDATE_W4_INFO','PAYROLL',
	'PAY_US_WEB_W4.UPDATE_W4_INFO','PAYROLL',
	'PQH_PROCESS_ACADEMIC_RANK.PROCESS_API',l_category_undefined,
	'PQH_PROCESS_EMP_REVIEW.PROCESS_API',l_category_undefined,
	'PQH_PROCESS_TENURE_STATUS.PROCESS_API',l_category_undefined,
	l_category_undefined)
	into l_transaction_category
	from hr_api_transaction_steps hats
	where hats.transaction_step_id = p_transaction_step_id;

Line: 217

 SELECT NVL(text_value,'N') into p_hrhd_val
 FROM wf_item_attribute_values
 where item_type= p_item_type and item_key = p_item_key
 and NAME = 'IS_HR_HELPDESK_AGENT';

Line: 256

      select selected_person_id
      into ln_requestor_person_id
      from hr_api_transactions
      where transaction_id=p_transaction_id;

Line: 271

      select creator_person_id
      into ln_requestor_person_id
      from hr_api_transactions
      where transaction_id=p_transaction_id;

Line: 285

      select transaction_ref_table,transaction_ref_id
      into lv_transaction_ref_table,lv_transaction_ref_id
      from hr_api_transactions
      where transaction_id=p_transaction_id;

Line: 292

          select main_appraiser_id
          into ln_requestor_person_id
          from per_appraisals
          where appraisal_id=lv_transaction_ref_id;

Line: 342

      select assignment_id into c_assignment_id from hr_api_transactions
      where transaction_id = p_transaction_id;

Line: 369

    select transaction_step_id
       into ln_step_id
       from hr_api_transaction_steps
       where hr_api_transaction_steps.transaction_id=p_transaction_id
--     and hr_api_transaction_steps.api_name='HR_PAY_RATE_SS.PROCESS_API';

Line: 408

    select transaction_step_id
       into ln_step_id
       from hr_api_transaction_steps
       where hr_api_transaction_steps.transaction_id=p_transaction_id
       and hr_api_transaction_steps.api_name='HR_PROCESS_ASSIGNMENT_SS.PROCESS_API';

Line: 448

    select transaction_step_id
       into ln_step_id
       from hr_api_transaction_steps
       where hr_api_transaction_steps.transaction_id=p_transaction_id
       and hr_api_transaction_steps.api_name='HR_SUPERVISOR_SS.PROCESS_API';

Line: 486

    select transaction_step_id
       into ln_step_id
       from hr_api_transaction_steps
       where hr_api_transaction_steps.transaction_id=p_transaction_id
       and hr_api_transaction_steps.api_name='HR_PERSON_ABSENCE_SWI.PROCESS_API';

Line: 524

    select transaction_step_id
       into ln_step_id
       from hr_api_transaction_steps
       where hr_api_transaction_steps.transaction_id=p_transaction_id
       and hr_api_transaction_steps.api_name='HR_TERMINATION_SS.PROCESS_API';

Line: 777

	    select sum(change_percentage)
	           into p_sum_percentage
		   from   per_pay_transactions ppt
	    where  parent_pay_transaction_id is null
	    and    ppt.transaction_step_id = ln_step_id;

Line: 855

       	    select sum(change_amount_n)
       	           into p_sum_amount
       		   from   per_pay_transactions ppt
       	    where  parent_pay_transaction_id is null
       	    and    ppt.transaction_step_id = ln_step_id;

Line: 898

    select api_name
       into ln_api_name
       from hr_api_transaction_steps
       where hr_api_transaction_steps.transaction_step_id=p_transaction_step_id;

Line: 938

    select ame_util.versiondatetostring(creation_date)
    into lv_creation_date_string
    from hr_api_transactions
   where transaction_id=p_transaction_id;

Line: 988

      select transaction_effective_date into lv_effective_date_string from hr_api_transactions
      where transaction_id = p_transaction_id;

Line: 1031

                            p_name =>'p_selected_person_sup_id'));

Line: 1035

                            p_name =>'p_selected_person_old_sup_id'));

Line: 1061

FUNCTION get_selected_person_id
(p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
        return varchar2 is
-- local variables
lv_procedure_name constant varchar2(22) := 'get_selected_person_id';

Line: 1066

lv_selected_person_id varchar2(10);

Line: 1081

    lv_selected_person_id := wf_engine.GetItemAttrNumber(itemtype => lv_item_type ,
                                              itemkey => lv_item_key,
                                              aname => 'CURRENT_PERSON_ID',
	                    ignore_notfound => true);

Line: 1087

      select selected_person_id into lv_selected_person_id from hr_api_transactions
      where transaction_id = p_transaction_id;

Line: 1096

return   fnd_number.number_to_canonical(lv_selected_person_id);

Line: 1103

END get_selected_person_id;

Line: 1320

      select transaction_ref_table,transaction_ref_id
      into lv_transaction_ref_table,lv_transaction_ref_id
      from hr_api_transactions
      where transaction_id=p_transaction_id;

Line: 1327

          select per_appraisals.system_type
          into lv_system_type
          from per_appraisals
          where appraisal_id=lv_transaction_ref_id;

Line: 1373

      select transaction_ref_table,transaction_ref_id
      into lv_transaction_ref_table,lv_transaction_ref_id
      from hr_api_transactions
      where transaction_id=p_transaction_id;

Line: 1380

          Select prl.step_value
          into ln_overall_rating
	  from per_appraisals appr, per_rating_levels prl
          where appraisal_id = lv_transaction_ref_id
          and appr.overall_performance_level_id = prl.rating_level_id;

Line: 1431

      select INFORMATION5 INTO lv_absence_type_id from hr_api_transaction_steps where transaction_step_id = ln_step_id;

Line: 1547

      select transaction_effective_date into ld_effective_date from hr_api_transactions
      where transaction_id = p_transaction_id;

Line: 1551

       select ppb.name
       into lv_salary_basis
       from pay_element_types_f pet,
       pay_input_values_f       piv,
       per_pay_bases            ppb
       where ppb.pay_basis_id=ln_pay_basis_id
       and ppb.input_value_id=piv.input_value_id
       and ld_effective_date  between
       piv.effective_start_date and
       piv.effective_end_date
       and piv.element_type_id=pet.element_type_id
       and ld_effective_date  between
       pet.effective_start_date and
       pet.effective_end_date;

Line: 1711

  SELECT ROUND(SUM(MONTHS_BETWEEN(
		decode(sign(p_eff_date-nvl(actual_termination_date, p_eff_date)),
                           -1, trunc(p_eff_date), nvl(actual_termination_date, trunc(p_eff_date))),
                trunc(ser.date_start))/12), 2) yos
  FROM per_periods_of_service ser
  WHERE ser.person_id = p_person_id
  AND ser.date_start <= p_eff_date;

Line: 1747

ln_length_of_service := getYOS(get_selected_person_id(p_transaction_id),trunc(sysdate));

Line: 1869

    select transaction_step_id
       into ln_step_id
       from hr_api_transaction_steps
       where hr_api_transaction_steps.transaction_id=p_transaction_id
       and hr_api_transaction_steps.api_name='HR_PROCESS_PERSON_SS.PROCESS_API';

Line: 1945

    select transaction_step_id
       into ln_step_id
       from hr_api_transaction_steps
       where hr_api_transaction_steps.transaction_id=p_transaction_id
       and hr_api_transaction_steps.api_name='HR_PROCESS_ADDRESS_SS.PROCESS_API';

Line: 2019

    select transaction_step_id
       into ln_step_id
       from hr_api_transaction_steps
       where hr_api_transaction_steps.transaction_id=p_transaction_id
       and hr_api_transaction_steps.api_name in ('HR_PROCESS_CONTACT_SS.PROCESS_API',
                                                 'HR_PROCESS_CONTACT_SS.PROCESS_CREATE_CONTACT_API')
       and rownum<2;

Line: 2097

    select transaction_step_id
       into ln_step_id
       from hr_api_transaction_steps
       where hr_api_transaction_steps.transaction_id=p_transaction_id
       and hr_api_transaction_steps.api_name='HR_CAED_SS.PROCESS_API';

Line: 2221

select start_date, end_date
from   per_time_periods
where  p_eff_date_csr > start_date
and    p_eff_date_csr <= end_date
and    payroll_id = p_payroll_id_csr;

Line: 2229

SELECT pay_basis_id
FROM   per_all_assignments_f
WHERE  assignment_id = l_assignment_id
AND    l_effective_date between effective_start_date
                            and effective_end_date
AND    assignment_type = 'E';

Line: 2327

select paf.position_id
 from per_all_assignments_f paf,
      per_all_people_f ppf,
      per_position_structures pps, per_pos_structure_versions ppsv,
      hr_api_transactions hat
where hat.transaction_id = p_transaction_id
and paf.person_id  = hat.creator_person_id
and trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and paf.primary_flag = 'Y'
and paf.assignment_type in ('E','C')
and paf.person_id = ppf.person_id
and ppf.business_group_id = pps.business_group_id(+)
and pps.primary_position_flag (+) = 'Y'
and pps.position_structure_id = ppsv.position_structure_id(+)
and trunc(sysdate) between ppsv.date_from(+) and nvl(ppsv.date_to(+),sysdate);