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_FIND_QUERY SQL Statements

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

Line: 113


,select_stmt               out nocopy varchar2) is
  type EmpCurTyp is ref cursor;

Line: 124


l_select_stmt_per VARCHAR2(20000);

Line: 125


l_select_stmt_per2 VARCHAR2(20000);

Line: 126


l_select_stmt_asg VARCHAR2(20000);

Line: 141


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

Line: 149


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


select description
from fnd_languages_vl
where language_code=p_lang_code;

Line: 162


select name
from  ben_benfts_grp
where benfts_grp_id=p_benfts_grp_id;

Line: 170


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


select count(*)
from per_all_assignments_f per_asg
where per_asg.person_id=p_person_id
and p_effective_date between per_asg.effective_start_date and per_asg.effective_end_date
and per_asg.assignment_type <> 'B' ; -- Added for fix of #3286659

Line: 186


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

Line: 195


select assignment_number
from per_all_assignments_f
where assignment_id = p_supervisor_assignment_id
and p_effective_date between effective_start_date and effective_end_date;

Line: 201


select date_start
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: 207


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

Line: 214


select name
from per_grades_vl
where grade_id=p_grade_id;

Line: 221


select name
from   ben_pgm_f
where  pgm_id = p_grade_ladder_pgm_id
and    p_effective_date between effective_start_date and effective_end_date;

Line: 232


select name
from hr_all_positions_f_tl
where position_id=p_position_id
and language = userenv('LANG');

Line: 241


select name
from per_jobs_vl
where job_id=p_job_id;

Line: 248


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 (+) = s.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: 269


select payroll_name
from pay_all_payrolls_f
where payroll_id=p_payroll_id
and p_effective_date between effective_start_date and effective_end_date;

Line: 276


select location_code
from hr_locations
where location_id=p_location_id;

Line: 282


select name
from per_recruitment_activities
where recruitment_activity_id=p_recruitment_activity_id;

Line: 288


select name
from hr_organization_units
where organization_id=p_organization_id;

Line: 294


select * from pay_people_groups
where people_group_id=p_people_group_id;

Line: 299


select * from hr_soft_coding_keyflex
where soft_coding_keyflex_id=p_soft_coding_keyflex_id;

Line: 304


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

Line: 313


select ppb.name
,      ppb.pay_basis
from   per_pay_bases ppb
where  ppb.pay_basis_id=p_pay_basis;

Line: 321


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


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


select name
from per_collective_agreements
where collective_agreement_id=p_collective_agreement_id;

Line: 359


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


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

Line: 375


select vendor_site_code
from po_vendor_sites
where vendor_site_id=p_vendor_site_id;

Line: 380


select segment1
from po_headers_all
where po_header_id = p_po_header_id;

Line: 385


select line_num
from po_lines_all
where po_line_id = p_po_line_id;

Line: 426


       l_where:=l_where||'(select '||p_child_column||' from '||
       p_child_table||' where upper('||p_child_meaning||')';

Line: 517


       l_where:=l_where||'(select '||p_child_column||' from '||
       p_child_table||' where '||p_child_meaning;

Line: 551


      l_where:='and per_asg.grade_id in (select grd_a.grade_id from per_grades_vl grd_a where grd_a.sequence';

Line: 557


      l_where:=l_where||'(select grd_a2.sequence from per_grades_vl grd_a2 where grd_a2.name like ('''
      ||p_value||''') ) )';

Line: 745


    elsif p_field_name='RESUME_LAST_UPDATED' then
      l_a_where_clause:=build_date_where('per.resume_last_updated',p_condition,p_value);

Line: 827


     || ' (SELECT ptu.person_id'
     ||   ' FROM per_person_type_usages_f ptu'
     ||   ' WHERE ptu.person_type_id = '||person_type_id
     ||   ' AND ' ||l_effective_date_clause
     ||   ' BETWEEN ptu.effective_start_date'
     ||   ' AND ptu.effective_end_date)';

Line: 837


       ||  ' (SELECT ptu.person_id'
       ||  ' FROM per_person_type_usages_f ptu'
       ||  ' ,per_person_types_tl ppttl'
       ||  ' WHERE ptu.person_type_id = ppttl.person_type_id'
       ||  ' AND UPPER(ppttl.user_person_type)'
       ||     ' LIKE '''||upper(replace(person_type,g_quote,g_quote||g_quote))||''''
       ||  ' AND ppttl.language = userenv(''LANG'')'
       ||  ' AND '||l_effective_date_clause
       ||     ' BETWEEN ptu.effective_start_date'
       ||     ' AND ptu.effective_end_date)';*/

Line: 850


       ||  ' (SELECT /*+ push_subq no_unnest */ 1'
       ||  ' FROM per_person_type_usages_f ptu'
       ||  ' ,per_person_types_tl ppttl'
       ||  ' WHERE ptu.person_type_id = ppttl.person_type_id'
       ||  ' AND ptu.person_id=per.person_id'
       ||  ' AND UPPER(ppttl.user_person_type)'
       ||     ' LIKE '''||upper(replace(person_type,g_quote,g_quote||g_quote))||''''
       ||  ' AND ppttl.language = userenv(''LANG'')'
       ||  ' AND '||l_effective_date_clause
       ||     ' BETWEEN ptu.effective_start_date'
       ||     ' AND ptu.effective_end_date)';

Line: 867


       ||   ' (SELECT ptu.person_id'
       ||   ' FROM per_person_type_usages_f ptu'
       ||   ' ,per_person_types ppt'
       ||   ' WHERE ptu.person_type_id = ppt.person_type_id'
       ||   ' AND ppt.system_person_type ='''||system_person_type||''''
       ||   ' AND '||l_effective_date_clause
       ||      ' BETWEEN ptu.effective_start_date'
       ||      ' AND ptu.effective_end_date)';

Line: 1280


    ||' (select 1'
    ||' from hr_leg_establishments_v hle'
    ||' where hle.organization_id = per_asg.establishment_id and upper(hle.name) like('''||upper(establishment)||'''))';

Line: 1362


  l_select_stmt_per:=
  'select per.person_id,nvl(per.order_name,per.full_name),count(*) '||l_from_clause||l_where_clause;

Line: 1365


    l_select_stmt_per:=l_select_stmt_per
     ||' AND (( EXISTS'
     ||   ' (SELECT 1'
     ||   ' FROM pay_restriction_values prv'
     ||   ' WHERE prv.customized_restriction_id = '
     ||             p_customized_restriction_id
     ||   ' AND prv.value IN'
     ||     ' (SELECT ptu.person_type_id'
     ||     ' FROM per_person_type_usages ptu'
     ||     ' WHERE ptu.person_id = per.person_id'
     ||     ' AND '||l_effective_date_clause
     ||        ' BETWEEN ptu.effective_start_date'
     ||        ' AND ptu.effective_end_date)'
     ||   ' AND NVL(prv.include_exclude_flag, ''I'') = ''I'' '
     ||   ' AND prv.restriction_code = ''PERSON_TYPE'')'
     || ' OR NOT EXISTS'
     ||   ' (SELECT 1'
     ||   ' FROM pay_restriction_values prv'
     ||   ' WHERE prv.customized_restriction_id ='
     ||             p_customized_restriction_id
     ||   ' AND NVL(prv.include_exclude_flag, ''I'') = ''I'' '
     ||   ' AND prv.restriction_code = ''PERSON_TYPE'') )'
     ||' AND NOT EXISTS '
     ||   ' (SELECT 1'
     ||   ' FROM pay_restriction_values prv2'
     ||   ' WHERE prv2.customized_restriction_id = '
     ||             p_customized_restriction_id
     ||   ' AND prv2.value IN'
     ||     ' (SELECT ptu2.person_type_id'
     ||     ' FROM per_person_type_usages ptu2'
     ||     ' WHERE ptu2.person_id = per.person_id'
     ||     ' AND '||l_effective_date_clause
     ||        ' BETWEEN ptu2.effective_start_date'
     ||        ' AND ptu2.effective_end_date)'
     ||   ' AND NVL(prv2.include_exclude_flag, ''I'') = ''E'' '
     ||   ' AND prv2.restriction_code = ''PERSON_TYPE''))';

Line: 1426


  l_select_stmt_per:=l_select_stmt_per||
  ' and ( (per.current_emp_or_apl_flag is null and per.current_npw_flag is null)';

Line: 1429


    l_select_stmt_per:=l_select_stmt_per||
    ' OR per.current_employee_flag =''Y'' ';

Line: 1433


    l_select_stmt_per:=l_select_stmt_per||
    ' OR per.current_applicant_flag =''Y'' ';

Line: 1437


    l_select_stmt_per:=l_select_stmt_per||
    ' OR per.current_npw_flag = ''Y'' ';

Line: 1440


  l_select_stmt_per:=l_select_stmt_per||')';

Line: 1446


  l_select_stmt_per:=l_select_stmt_per||' group by per.person_id,nvl(per.order_name,per.full_name) order by nvl(per.order_name,per.full_name)';

Line: 1447


hr_utility.set_location('r'||l_select_stmt_per,2001);

Line: 1449


  select_stmt:=l_from_clause||l_where_clause;

Line: 1450


  l_select_stmt_asg:='select per_asg.assignment_id '||l_from_clause;

Line: 1452


    l_select_stmt_asg:=l_select_stmt_asg||',per_all_assignments_f per_asg';

Line: 1454


  l_select_stmt_asg:=l_select_stmt_asg||l_where_clause;

Line: 1456


    l_select_stmt_asg:=l_select_stmt_asg||' and per.person_id=per_asg.person_id and '
    ||l_effective_date||
    ' between per_asg.effective_start_date and per_asg.effective_end_date ';

Line: 1460


  l_select_stmt_asg:=l_select_stmt_asg||'and per_asg.assignment_type <> ''B''';-- Added for fix of #3286659

Line: 1461


  l_select_stmt_asg:=l_select_stmt_asg||' and per.person_id=:1';

Line: 1462


l_select_stmt_per2:=l_select_stmt_per;

Line: 1464


hr_utility.set_location('r'||l_select_stmt_per,2000);

Line: 1466


while length(l_select_stmt_per2)>0 loop

  hr_utility.set_location(substr(l_select_stmt_per2,1,70),1);

Line: 1469


  l_select_stmt_per2:=substr(l_select_stmt_per2,71);

Line: 1473


hr_utility.set_location('hidd'||l_select_stmt_per,1000);

Line: 1475


open emp_cv for l_select_stmt_per;

Line: 1555


      out_rec.resume_last_updated           :=per_rec.resume_last_updated;

Line: 1671


      execute immediate l_select_stmt_asg into l_assignment_id using per_rec.person_id;

Line: 2383


  procedure insert_varchar2(p_query_id number
                           ,p_field varchar2
                           ,p_value varchar2
                           ) is
  begin
    insert into per_query_criteria
    (query_id
    ,field
    ,field_type
    ,varchar2_value
    ,number_value
    ,date_value
    ,object_version_number)
    values
    (p_query_id
    ,p_field
    ,'V'
    ,replace(p_value,g_quote,g_quote||g_quote)
    ,null
    ,null
    ,1);

Line: 2404


  end insert_varchar2;

Line: 2406


  procedure insert_number  (p_query_id number
                           ,p_field varchar2
                           ,p_value number
                           ) is
  begin
    insert into per_query_criteria
    (query_id
    ,field
    ,field_type
    ,varchar2_value
    ,number_value
    ,date_value
    ,object_version_number)
    values
    (p_query_id
    ,p_field
    ,'N'
    ,null
    ,p_value
    ,null
    ,1);

Line: 2427


  end insert_number;

Line: 2429


  procedure insert_date    (p_query_id number
                           ,p_field varchar2
                           ,p_value date
                           ) is
  begin
    insert into per_query_criteria
    (query_id
    ,field
    ,field_type
    ,varchar2_value
    ,number_value
    ,date_value
    ,object_version_number)
    values
    (p_query_id
    ,p_field
    ,'D'
    ,null
    ,null
    ,p_value
    ,1);

Line: 2450


  end insert_date;

Line: 2562


    insert_number(l_query_id,'BUSINESS_GROUP_ID',business_group_id);

Line: 2566


        insert_varchar2(l_query_id,'BUSINESS_GROUP_NAME',business_group_name);

Line: 2569


    insert_number(l_query_id,'PERSON_ID',person_id);

Line: 2572


    insert_varchar2(l_query_id,'PERSON_TYPE',person_type);

Line: 2575


    insert_varchar2(l_query_id,'SYSTEM_PERSON_TYPE',system_person_type);

Line: 2578


    insert_number(l_query_id,'PERSON_TYPE_ID',person_type_id);

Line: 2581


    insert_varchar2(l_query_id,'LAST_NAME',last_name);

Line: 2584


    insert_date(l_query_id,'START_DATE',start_date);

Line: 2587


    insert_date(l_query_id,'HIRE_DATE',hire_date);

Line: 2590


    insert_varchar2(l_query_id,'APPLICANT_NUMBER',applicant_number);

Line: 2593


    insert_date(l_query_id,'DATE_OF_BIRTH',date_of_birth);

Line: 2596


    insert_varchar2(l_query_id,'EMAIL_ADDRESS',email_address);

Line: 2599


    insert_varchar2(l_query_id,'EMPLOYEE_NUMBER',employee_number);

Line: 2603


    insert_varchar2(l_query_id,'NPW_NUMBER',npw_number);

Line: 2606


    insert_varchar2(l_query_id,'PROJECT_TITLE',project_title);

Line: 2609


    insert_number(l_query_id,'VENDOR_ID',vendor_id);

Line: 2612


    insert_varchar2(l_query_id,'VENDOR_NAME',vendor_name);

Line: 2615


    insert_varchar2(l_query_id,'VENDOR_EMPLOYEE_NUMBER',vendor_employee_number);

Line: 2618


    insert_varchar2(l_query_id,'VENDOR_ASSIGNMENT_NUMBER',vendor_assignment_number);

Line: 2621


    insert_number(l_query_id,'VENDOR_SITE_ID',vendor_site_id);

Line: 2624


    insert_varchar2(l_query_id,'VENDOR_SITE_CODE',vendor_site_code);

Line: 2627


    insert_number(l_query_id,'PO_HEADER_ID',po_header_id);

Line: 2630


    insert_varchar2(l_query_id,'PO_HEADER_NUM',po_header_num);

Line: 2633


    insert_number(l_query_id,'PO_LINE_ID',po_line_id);

Line: 2636


    insert_varchar2(l_query_id,'PO_LINE_NUM',po_line_num);

Line: 2640


    insert_varchar2(l_query_id,'FIRST_NAME',first_name);

Line: 2643


    insert_varchar2(l_query_id,'FULL_NAME',full_name);

Line: 2646


    insert_varchar2(l_query_id,'TITLE',title);

Line: 2649


    insert_varchar2(l_query_id,'MIDDLE_NAMES',middle_names);

Line: 2652


    insert_varchar2(l_query_id,'NATIONALITY_MEANING',nationality_meaning);

Line: 2655


    insert_varchar2(l_query_id,'NATIONALITY',nationality);

Line: 2658


    insert_varchar2(l_query_id,'NATIONAL_IDENTIFIER',national_identifier);

Line: 2662


    insert_varchar2(l_query_id,'REGISTERED_DISABLED_FLAG',registered_disabled_flag);

Line: 2666


    insert_varchar2(l_query_id,'REGISTERED_DISABLED',registered_disabled);

Line: 2669


    insert_varchar2(l_query_id,'SEX_MEANING',sex_meaning);

Line: 2672


    insert_varchar2(l_query_id,'SEX',sex);

Line: 2675


    insert_varchar2(l_query_id,'BENEFIT_GROUP',benefit_group);

Line: 2678


    insert_number(l_query_id,'BENEFIT_GROUP_ID',benefit_group_id);

Line: 2681


    insert_varchar2(l_query_id,'GRADE',grade);

Line: 2684


    insert_number(l_query_id,'GRADE_ID',grade_id);

Line: 2687


    insert_varchar2(l_query_id,'GRADE_LADDER',grade_ladder);

Line: 2690


    insert_number(l_query_id,'GRADE_LADDER_PGM_ID',grade_ladder_pgm_id);

Line: 2693


    insert_varchar2(l_query_id,'POSITION',position);

Line: 2696


    insert_number(l_query_id,'POSITION_ID',position_id);

Line: 2699


    insert_varchar2(l_query_id,'JOB',job);

Line: 2702


    insert_number(l_query_id,'JOB_ID',job_id);

Line: 2705


    insert_varchar2(l_query_id,'ASSIGNMENT_STATUS_TYPE',assignment_status_type);

Line: 2708


    insert_number(l_query_id,'ASSIGNMENT_STATUS_TYPE_ID',assignment_status_type_id);

Line: 2711


    insert_varchar2(l_query_id,'PAYROLL',payroll);

Line: 2714


    insert_number(l_query_id,'PAYROLL_ID',payroll_id);

Line: 2717


    insert_varchar2(l_query_id,'LOCATION',location);

Line: 2720


    insert_number(l_query_id,'LOCATION_ID',location_id);

Line: 2723


    insert_varchar2(l_query_id,'SUPERVISOR',supervisor);

Line: 2726


    insert_number(l_query_id,'SUPERVISOR_ID',supervisor_id);

Line: 2729


    insert_varchar2(l_query_id,'SUPERVISOR_ASSIGNMENT_NUMBER',supervisor_assignment_number);

Line: 2732


    insert_number(l_query_id,'SUPERVISOR_ASSIGNMENT_ID',supervisor_assignment_id);

Line: 2735


    insert_varchar2(l_query_id,'RECRUITMENT_ACTIVITY',recruitment_activity);

Line: 2738


    insert_number(l_query_id,'RECRUITMENT_ACTIVITY_ID',recruitment_activity_id);

Line: 2741


    insert_varchar2(l_query_id,'ORGANIZATION',organization);

Line: 2744


    insert_number(l_query_id,'ORGANIZATION_ID',organization_id);

Line: 2747


    insert_varchar2(l_query_id,'PEOPLE_GROUP',people_group);

Line: 2750


    insert_number(l_query_id,'PEOPLE_GROUP_ID',people_group_id);

Line: 2753


    insert_varchar2(l_query_id,'VACANCY',vacancy);

Line: 2756


    insert_number(l_query_id,'VACANCY_ID',vacancy_id);

Line: 2759


    insert_varchar2(l_query_id,'REQUISITION',requisition);

Line: 2762


    insert_number(l_query_id,'REQUISITION_ID',requisition_id);

Line: 2765


    insert_varchar2(l_query_id,'SALARY_BASIS',salary_basis);

Line: 2768


    insert_number(l_query_id,'PAY_BASIS_ID',pay_basis_id);

Line: 2771


    insert_varchar2(l_query_id,'BARGAINING_UNIT_CODE_MEANING',bargaining_unit_code_meaning);

Line: 2774


    insert_varchar2(l_query_id,'BARGAINING_UNIT_CODE',bargaining_unit_code);

Line: 2777


    insert_varchar2(l_query_id,'EMPLOYMENT_CATEGORY_MEANING',employment_category_meaning);

Line: 2780


    insert_varchar2(l_query_id,'EMPLOYMENT_CATEGORY',employment_category);

Line: 2783


    insert_varchar2(l_query_id,'ESTABLISHMENT',establishment);

Line: 2786


    insert_number(l_query_id,'ESTABLISHMENT_ID',establishment_id);

Line: 2789


    insert_date(l_query_id,'PROJECTED_HIRE_DATE',projected_hire_date);

Line: 2792


    insert_varchar2(l_query_id,'SECURE',secure);

Line: 2795


    insert_varchar2(l_query_id,'FIELD1_NAME',field1_name);

Line: 2798


    insert_varchar2(l_query_id,'FIELD1_CONDITION_CODE',field1_condition_code);

Line: 2801


    insert_varchar2(l_query_id,'FIELD1_VALUE',field1_value);

Line: 2804


    insert_varchar2(l_query_id,'FIELD2_NAME',field2_name);

Line: 2807


    insert_varchar2(l_query_id,'FIELD2_CONDITION_CODE',field2_condition_code);

Line: 2810


    insert_varchar2(l_query_id,'FIELD2_VALUE',field2_value);

Line: 2813


    insert_varchar2(l_query_id,'FIELD3_NAME',field3_name);

Line: 2816


    insert_varchar2(l_query_id,'FIELD3_CONDITION_CODE',field3_condition_code);

Line: 2819


    insert_varchar2(l_query_id,'FIELD3_VALUE',field3_value);

Line: 2822


    insert_varchar2(l_query_id,'FIELD4_NAME',field4_name);

Line: 2825


    insert_varchar2(l_query_id,'FIELD4_CONDITION_CODE',field4_condition_code);

Line: 2828


    insert_varchar2(l_query_id,'FIELD4_VALUE',field4_value);

Line: 2831


    insert_varchar2(l_query_id,'FIELD5_NAME',field5_name);

Line: 2834


    insert_varchar2(l_query_id,'FIELD5_CONDITION_CODE',field5_condition_code);

Line: 2837


    insert_varchar2(l_query_id,'FIELD5_VALUE',field5_value);

Line: 2849


  select varchar2_value
  from per_query_criteria
  where query_id=p_query_id
  and field=p_field;

Line: 2866


  select number_value
  from per_query_criteria
  where query_id=p_query_id
  and field=p_field;

Line: 2883


  select date_value
  from per_query_criteria
  where query_id=p_query_id
  and field=p_field;

Line: 2906


  l_select_stmt_per VARCHAR2(20000);

Line: 3010


,select_stmt => l_select_stmt_per);