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.

[Home] [Help]

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: 118


SELECT
  pptuf.person_type_id,
  ppttl.user_person_type,
  ppt.system_person_type
FROM
  per_person_types_tl ppttl,
  per_person_types ppt,
  per_person_types ppt1,
  per_person_type_usages_f pptuf
WHERE
  pptuf.person_id = p_person_id
  and l_effective_date between pptuf.effective_start_date and pptuf.effective_end_date
  and ppt1.person_type_id = p_person_type_id
  and ppt1.system_person_type = ppt.system_person_type
  and ppt.person_type_id=pptuf.person_type_id
  and ppt.active_flag = 'Y'
  and ppttl.person_type_id = ppt.person_type_id
  and ppttl.language = userenv('LANG');

Line: 138


  SELECT ptu.person_type_id FROM
  per_person_type_usages_f ptu,
  per_person_types ppt WHERE
  ptu.person_id = p_person_id
  and l_effective_date between ptu.effective_start_date and ptu.effective_end_date
  and ptu.person_type_id = ppt.person_type_id and ppt.system_person_type=p_system_person_type;

Line: 148


select description
from fnd_languages_vl
where language_code=p_lang_code;

Line: 154


select name
from  ben_benfts_grp
where benfts_grp_id=p_benfts_grp_id;

Line: 159


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

Line: 169


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: 178


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: 186


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

Line: 199


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

Line: 210


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: 220


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: 253

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: 261

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: 281

select name
from per_recruitment_activities
where recruitment_activity_id=p_recruitment_activity_id;

Line: 287

select * from pay_people_groups
where people_group_id=p_people_group_id;

Line: 292

select * from hr_soft_coding_keyflex
where soft_coding_keyflex_id=p_soft_coding_keyflex_id;

Line: 297

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: 307

  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: 332

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: 339

select name
from per_collective_agreements
where collective_agreement_id=p_collective_agreement_id;

Line: 345

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: 353

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: 362

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)
and p1.date_to >= p_effective_date;    -- Fix For Bug # 8494017

Line: 374

select *
from per_deployment_factors
where person_id=p_person_id;

Line: 380

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: 391

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

Line: 398

select territory_short_name
from fnd_territories_vl
where territory_code=p_territory_code;

Line: 404

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: 422

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

Line: 427

select vendor_site_code
from po_vendor_sites_all
where vendor_site_id=p_vendor_site_id;

Line: 432

select segment1
from po_headers_all
where po_header_id = p_po_header_id;

Line: 437

select line_num
from po_lines_all
where po_line_id = p_po_line_id;

Line: 442

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: 448

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: 503

    out_rec.person_update_allowed:='FALSE';

Line: 504

    out_rec.asg_update_allowed:='FALSE';

Line: 505

    out_rec.tax_update_allowed:='FALSE';

Line: 519

    out_rec.person_update_allowed:='TRUE';

Line: 521

    out_rec.person_update_allowed:='FALSE';

Line: 615

  out_rec.resume_last_updated           :=per_rec.resume_last_updated;

Line: 1779

      out_rec.asg_update_allowed:='FALSE';

Line: 1790

        out_rec.asg_update_allowed:='TRUE';

Line: 1792

        out_rec.asg_update_allowed:='FALSE';

Line: 2480

  ,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
  );