APPS.PQH_FR_QUOTA_CHECK SQL Statements

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

Line: 21

procedure delete_rows is
l_proc varchar2(60) := g_package||'delete_rows';

Line: 25

g_quota_grid.delete;

Line: 29

Procedure Update_grid(p_corp_id in number,
                      p_corp_name in varchar2,
                      p_grade_id in number,
                      p_grade_name in varchar2,
                      p_effective_date in date) is

l_number_of_rows number;

Line: 38

l_proc varchar2(60) := g_package||'Update_grid';

Line: 82

end Update_grid;

Line: 98

  Select count(asg.assignment_id)
 into   l_corp_population
 From Per_all_assignments_f asg,
      pqh_corps_definitions corp,
      per_all_people_f per
 Where asg.primary_flag = 'Y'
 And asg.business_group_id = p_business_group_id
 And asg.assignment_status_type_id in (1,2)
 And l_effective_date between asg.effective_start_date And asg.effective_end_date
 And asg.grade_ladder_pgm_id = corp.ben_pgm_id
 And corp.corps_definition_id = l_corp_id
 And per.person_id = asg.person_id
 And l_effective_date between per.effective_start_date And per.effective_end_date
 And per.per_information15 = '01';

Line: 132

Select count(asg.assignment_id)
into l_grade_population
From Per_all_assignments_f asg,
     pqh_corps_definitions corp,
     per_all_people_f per
Where asg.primary_flag = 'Y'
And asg.business_group_id = p_business_group_id
And asg.assignment_status_type_id in (1,2)
And p_effective_date between asg.effective_start_date And asg.effective_end_date
And asg.grade_ladder_pgm_id = corp.ben_pgm_id
And corp.corps_definition_id = p_corp_id
And grade_id = p_grade_id
And per.person_id = asg.person_id
And p_effective_date between per.effective_start_date And per.effective_end_date
And per.per_information15 = '01';

Line: 162

select nvl(pei.pgi_information1,'N') Quota_flag
into l_quota_flag
from ben_pgm_extra_info pei,
     pqh_corps_definitions corps
where pei.information_type = 'PQH_FR_CORP_INFO'
and pei.pgm_id = corps.ben_pgm_id
and corps.corps_definition_id = p_corp_id;

Line: 184

Select Information4 Percentage,
Information8 cond_type,
Information30 Grade_ids
From pqh_corps_extra_info
Where corps_definition_id = p_corp_id
And information3 = p_grade_id
And information_type = 'GRADE';

Line: 258

select pil.person_id person_id,
       corp.corps_definition_id corp_id,
       corp.name corp_name,
       elc.pgm_id program_id,
       grades.grade_id grade_id,
       grades.name grade_name,
       pil.business_group_id business_group_id
From ben_elig_per_elctbl_chc elc, ben_per_in_ler pil,
     ben_pl_f pl, per_grades grades,
     pqh_corps_definitions corp
Where elc.elig_per_elctbl_chc_id = l_elctbl_chc_id
and elc.per_in_ler_id = pil.per_in_ler_id
And grades.grade_id = pl.mapping_table_pk_id
And pl.mapping_table_name = 'PER_GRADES'
and p_effective_date between pl.effective_start_date and pl.effective_end_date
And corp.ben_pgm_id = elc.pgm_id
And pl.pl_id = elc.pl_id
and PER_IN_LER_STAT_CD = 'STRTD';

Line: 280

select grade_ladder_pgm_id pgm_id,
       grade_id grade_id,
       per.per_information15 agent_type
from per_all_assignments_f asg,
     per_all_people_f per
where asg.person_id = l_elctbl_chc_det_rec.person_id
and primary_flag = 'Y'
and p_effective_date between asg.effective_start_date and asg.effective_end_date
and p_effective_date between per.effective_start_date and per.effective_end_date
and per.person_id = asg.person_id;

Line: 292

Select business_group_id, legislation_code
from   per_business_groups
where business_group_id = l_elctbl_chc_det_rec.business_group_id;

Line: 326

     hr_utility.set_location('Before Update Grid Call',5);

Line: 327

    Update_grid(p_corp_id => l_elctbl_chc_det_rec.corp_id,
            p_corp_name => l_elctbl_chc_det_rec.corp_name,
            p_grade_id => l_elctbl_chc_det_rec.grade_id,
            p_grade_name => l_elctbl_chc_det_rec.grade_name,
            p_effective_date => p_effective_date );

Line: 373

      select count(elc.elig_per_elctbl_chc_id )
      into l_already_app
      from  ben_elig_per_elctbl_chc elc,
            ben_per_in_ler pil,
            ben_pl_f pl,
            pqh_corps_definitions corp
      where nvl(elc.approval_status_cd,'PQH_GSP_NP') = 'PQH_GSP_NP'
      and elc.in_pndg_wkflow_flag = 'Y'
      and pil.per_in_ler_id = elc.per_in_ler_id
      and pil.per_in_ler_stat_cd = 'STRTD'
      and corp.corps_definition_id = g_quota_grid(table_row).corp_id
      and elc.pgm_id = corp.ben_pgm_id
      and pl.mapping_table_name = 'PER_GRADES'
      and pl.mapping_table_pk_id = g_quota_grid(table_row).grade_id
      and g_quota_grid(table_row).effective_date between pl.effective_start_date and pl.effective_end_date
      and elc.pl_id = pl.pl_id
      and elc.business_group_id = p_business_group_id;

Line: 460

 select name, corps_definition_id  into l_corp_name, l_corp_id
  from pqh_corps_definitions
  where ben_pgm_id  = p_corp_id;

Line: 474

  select count(elc.elig_per_elctbl_chc_id )
  into l_already_app
  from  ben_elig_per_elctbl_chc elc,
      ben_per_in_ler pil,
      ben_pl_f pl,
      pqh_corps_definitions corp
  where elc.approval_status_cd = 'PQH_GSP_A'
  and pil.per_in_ler_id = elc.per_in_ler_id
  and pil.per_in_ler_stat_cd = 'STRTD'
  and corp.corps_definition_id = l_corp_id
  and elc.pgm_id = corp.ben_pgm_id
  and pl.mapping_table_name = 'PER_GRADES'
  and pl.mapping_table_pk_id = p_grade_id
  and p_effective_date between pl.effective_start_date and pl.effective_end_date
  and elc.pl_id = pl.pl_id
  and elc.business_group_id = p_business_group_id;

Line: 491

  select name into l_grade_name
 from per_grades where
 grade_id = p_grade_id;