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.PER_QH_MAINTAIN_QUERY SQL Statements

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

Line: 22


select x.name
from per_business_groups x , per_all_people_f papf
where x.business_group_id = papf.business_group_id
and papf.person_id=p_person_id;

Line: 30


 select x.legislation_code
 from per_business_groups x
 where x.business_group_id=p_business_group_id;

Line: 35


  select
  hft.form_template_id
  from hr_form_templates_b hft
  ,    fnd_form ff
  where
  ff.form_name='PERWSQHM'
  and ff.application_id=800
  and ff.form_id=hft.form_id
  and hft.template_name=p_template_name
  and (hft.legislation_code = p_legislation_code
      or hft.legislation_code is null);

Line: 56


    SELECT tim.template_item_id
          ,itptl.default_value
      FROM hr_template_items_b tim
      ,    hr_form_items_b fim
      ,    hr_item_properties_b itpb
      ,    hr_item_properties_tl itptl
     WHERE fim.full_item_name = p_full_item_name
       AND tim.form_item_id = fim.form_item_id
       AND tim.form_template_id = p_form_template_id
       AND itpb.template_item_id (+) = tim.template_item_id
       AND itptl.item_property_id (+) = itpb.item_property_id
       AND itptl.language (+) = USERENV('LANG');

Line: 76


    SELECT itptl.default_value
      FROM hr_item_contexts icx
          ,hr_item_properties_b itpb
          ,hr_item_properties_tl itptl
          ,hr_template_item_contexts_b tic
     WHERE icx.segment1 = p_emp_apl_flag
       AND icx.item_context_id = tic.item_context_id
       AND tic.context_type = 'QH_PER_TYPE'
       AND tic.template_item_id = p_template_item_id
       AND itpb.template_item_context_id (+) = tic.template_item_context_id
       AND itptl.item_property_id (+) = itpb.item_property_id
       AND itptl.language (+) = USERENV('LANG');

Line: 98


select *
from per_all_people_f
where person_id=p_person_id
and p_effective_date between effective_start_date and effective_end_date;

Line: 105


SELECT
  ppttl.user_person_type,
  ppt.system_person_type
FROM
  per_person_types_tl ppttl,
  per_person_types ppt
WHERE
  ppt.person_type_id=p_person_type_id
  and ppt.active_flag = 'Y'
  and ppttl.person_type_id = ppt.person_type_id
  and ppttl.language = userenv('LANG');

Line: 119


select description
from fnd_languages_vl
where language_code=p_lang_code;

Line: 125


select name
from  ben_benfts_grp
where benfts_grp_id=p_benfts_grp_id;

Line: 130


select rule_type
from pay_legislative_field_info plfi
where plfi.field_name = p_type
and plfi.legislation_code = p_legislation_code;

Line: 140


select * from per_applications
where person_id=p_person_id
and p_effective_date between date_received and nvl(date_end,p_effective_date);

Line: 149


select * from per_periods_of_service
where person_id=p_person_id
and p_effective_date between date_start and nvl(final_process_date,p_effective_date);

Line: 157


select * from per_periods_of_service
where person_id=p_person_id
and date_start <= p_effective_date
order by date_start desc;

Line: 170


select * from per_periods_of_placement
where person_id=p_person_id
and date_start <= p_effective_date
order by date_start desc;

Line: 181


select *
from per_addresses
where person_id=p_person_id
and primary_flag='Y'
and p_effective_date between date_from and nvl(date_to,p_effective_date);

Line: 191


select 0,phone_id,date_from,date_to,phone_number,object_version_number
from per_phones
where parent_id=p_person_id
and parent_table='PER_ALL_PEOPLE_F'
and phone_type=p_type
and p_effective_date between date_from and nvl(date_to,p_effective_date)
UNION
select months_between(p_effective_date,date_to),phone_id,date_from,date_to,phone_number,object_version_number
from per_phones
where parent_id=p_person_id
and parent_table='PER_ALL_PEOPLE_F'
and phone_type=p_type
and p_effective_date>date_to
UNION
select months_between(date_from,p_effective_date),phone_id,date_from,date_to,phone_number,object_version_number
from per_phones
where parent_id=p_person_id
and parent_table='PER_ALL_PEOPLE_F'
and phone_type=p_type
and p_effective_date


Line: 224

select *
from per_all_assignments_f
where assignment_id=p_assignment_id
and p_effective_date between effective_start_date and effective_end_date;

Line: 232

SELECT
  nvl(atl.user_status     ,stl.user_status),
  nvl(a.per_system_status ,s.per_system_status)
FROM
  per_ass_status_type_amends_tl atl,
  per_ass_status_type_amends a,
  per_assignment_status_types_tl stl,
  per_assignment_status_types s
WHERE
  s.assignment_status_type_id= p_assignment_status_type_id and
  a.assignment_status_type_id (+) = s.assignment_status_type_id and
  a.business_group_id (+) +0= p_business_group_id and
  nvl(a.active_flag, s.active_flag) = 'Y' and
  a.ass_status_type_amend_id = atl.ass_status_type_amend_id (+) and
  decode(atl.language,null,'1',atl.language) = decode(atl.language,null,'1',userenv('LANG')) and
  s.assignment_status_type_id = stl.assignment_status_type_id and
  stl.language = userenv('LANG');

Line: 252

select name
from per_recruitment_activities
where recruitment_activity_id=p_recruitment_activity_id;

Line: 258

select * from pay_people_groups
where people_group_id=p_people_group_id;

Line: 263

select * from hr_soft_coding_keyflex
where soft_coding_keyflex_id=p_soft_coding_keyflex_id;

Line: 268

select vac.name
,      rec.name
from   per_requisitions rec
,      per_vacancies vac
where vacancy_id=p_vacancy_id
and   vac.requisition_id=rec.requisition_id;

Line: 278

  select psp.spinal_point spinal_point
  , count(*) step
  from per_spinal_points psp
  , per_spinal_points psp2
  , per_spinal_point_steps_f psps
  , per_spinal_point_steps_f psps2
  where psp.spinal_point_id = psps.spinal_point_id
  and psps.grade_spine_id = psps2.grade_spine_id
  and psp2.spinal_point_id = psps2.spinal_point_id
  and psps.step_id=p_special_ceiling_step_id
  and psp.sequence >= psp2.sequence
  and p_effective_date between psps.effective_start_date
      and psps.effective_end_date
  and p_effective_date between psps2.effective_start_date
      and psps2.effective_end_date
  group by psp.spinal_point
  , psps.step_id
  , psps.sequence
  , psps.effective_start_date
  , psps.effective_end_date
  order by 2;

Line: 303

select reference
from per_contracts_f
where contract_id=p_contract_id
and p_effective_date between effective_start_date and effective_end_date;

Line: 310

select name
from per_collective_agreements
where collective_agreement_id=p_collective_agreement_id;

Line: 316

select id_flex_structure_name
from fnd_id_flex_structures_vl
where id_flex_code= 'CAGR'
and   application_id = 800
and   id_flex_num=p_id_flex_num;

Line: 324

select descriptive_flex_context_name
from   fnd_descr_flex_contexts_vl
where  descriptive_flexfield_name='Address Structure'
and    application_id=800
and    descriptive_flex_context_code=p_style;

Line: 333

select *
from per_pay_proposals p1
where p1.assignment_id=p_assignment_id
and   p1.change_date=
     (select max(p2.change_date)
      from per_pay_proposals p2
      where p2.assignment_id=p_assignment_id
      and change_date<=p_effective_date);

Line: 344

select *
from per_deployment_factors
where person_id=p_person_id;

Line: 350

select lookup_code
from hr_lookups
where lookup_type=p_lookup_type
and meaning=p_meaning
and enabled_flag='Y'
and p_effective_date between
nvl(start_date_active,p_effective_date) and nvl(end_date_active,p_effective_date);

Line: 361

select *
from per_checklist_items
where person_id=p_person_id
and item_code=p_item_code;

Line: 368

select territory_short_name
from fnd_territories_vl
where territory_code=p_territory_code;

Line: 374

SELECT ppb.name
,      ppb.pay_basis
,PET.INPUT_CURRENCY_CODE
, PIV.UOM
FROM PAY_ELEMENT_TYPES_F PET
, PAY_INPUT_VALUES_F       PIV
, PER_PAY_BASES            PPB
WHERE PPB.PAY_BASIS_ID=p_pay_basis_id
AND PPB.INPUT_VALUE_ID=PIV.INPUT_VALUE_ID
AND p_effective_date  BETWEEN
PIV.EFFECTIVE_START_DATE AND
PIV.EFFECTIVE_END_DATE
AND PIV.ELEMENT_TYPE_ID=PET.ELEMENT_TYPE_ID
AND p_effective_date  BETWEEN
PET.EFFECTIVE_START_DATE AND
PET.EFFECTIVE_END_DATE;

Line: 392

select vendor_name
from po_vendors pov
where pov.vendor_id = p_vendor_id;

Line: 397

select vendor_site_code
from po_vendor_sites_all
where vendor_site_id=p_vendor_site_id;

Line: 402

select segment1
from po_headers_all
where po_header_id = p_po_header_id;

Line: 407

select line_num
from po_lines_all
where po_line_id = p_po_line_id;

Line: 412

select sob.name, sob.chart_of_accounts_id
from gl_sets_of_books sob
where sob.set_of_books_id = p_set_of_books_id;

Line: 418

select gr.grade_rule_id
      ,pr.name rate_name
      ,gr.rate_id
      ,SUBSTR(hr_general.decode_lookup('RATE_BASIS',pr.rate_basis),1,80) rate_basis
      ,fnd_asr.meaning asg_rate_type_name
      ,gr.currency_code
      ,f.name rate_currency
      ,gr.value
      ,gr.effective_start_date
      ,gr.effective_end_date
      ,gr.object_version_number
from  pay_grade_rules_f gr
     ,pay_rates pr
     ,fnd_currencies_vl f
     ,fnd_lookups fnd_asr
where gr.rate_type = 'A'
and   gr.grade_or_spinal_point_id = p_assignment_id
and   p_effective_date between gr.effective_start_date and gr.effective_end_date
and   gr.rate_id = pr.rate_id
and   gr.currency_code = f.currency_code
and  fnd_asr.lookup_code(+)=pr.asg_rate_type
and  fnd_asr.lookup_type(+)='PRICE DIFFERENTIALS';

Line: 473

    out_rec.person_update_allowed:='FALSE';

Line: 474

    out_rec.asg_update_allowed:='FALSE';

Line: 475

    out_rec.tax_update_allowed:='FALSE';

Line: 489

    out_rec.person_update_allowed:='TRUE';

Line: 491

    out_rec.person_update_allowed:='FALSE';

Line: 581

  out_rec.resume_last_updated           :=per_rec.resume_last_updated;

Line: 1745

      out_rec.asg_update_allowed:='FALSE';

Line: 1756

        out_rec.asg_update_allowed:='TRUE';

Line: 1758

        out_rec.asg_update_allowed:='FALSE';

Line: 2446

  ,tax_update_allowed  => out_rec.tax_update_allowed
  ,p_person_id         => p_person_id
  ,p_assignment_id     => p_assignment_id
  ,p_legislation_code  => l_legislation_code
  ,p_effective_date    => p_effective_date
  );