[Home] [Help]
5: g_business_group_id number;
6:
7: TYPE quota_rec IS
8: RECORD (corp_id number,
9: corp_name pqh_corps_definitions.name%type,
10: grade_id number,
11: grade_name per_grades.name%type,
12: cur_per_app number,
13: effective_date date);
97:
98: Select count(asg.assignment_id)
99: into l_corp_population
100: From Per_all_assignments_f asg,
101: pqh_corps_definitions corp,
102: per_all_people_f per
103: Where asg.primary_flag = 'Y'
104: And asg.business_group_id = p_business_group_id
105: And asg.assignment_status_type_id in (1,2)
131:
132: Select count(asg.assignment_id)
133: into l_grade_population
134: From Per_all_assignments_f asg,
135: pqh_corps_definitions corp,
136: per_all_people_f per
137: Where asg.primary_flag = 'Y'
138: And asg.business_group_id = p_business_group_id
139: And asg.assignment_status_type_id in (1,2)
161:
162: select nvl(pei.pgi_information1,'N') Quota_flag
163: into l_quota_flag
164: from ben_pgm_extra_info pei,
165: pqh_corps_definitions corps
166: where pei.information_type = 'PQH_FR_CORP_INFO'
167: and pei.pgm_id = corps.ben_pgm_id
168: and corps.corps_definition_id = p_corp_id;
169:
263: grades.name grade_name,
264: pil.business_group_id business_group_id
265: From ben_elig_per_elctbl_chc elc, ben_per_in_ler pil,
266: ben_pl_f pl, per_grades grades,
267: pqh_corps_definitions corp
268: Where elc.elig_per_elctbl_chc_id = l_elctbl_chc_id
269: and elc.per_in_ler_id = pil.per_in_ler_id
270: And grades.grade_id = pl.mapping_table_pk_id
271: And pl.mapping_table_name = 'PER_GRADES'
374: into l_already_app
375: from ben_elig_per_elctbl_chc elc,
376: ben_per_in_ler pil,
377: ben_pl_f pl,
378: pqh_corps_definitions corp
379: where nvl(elc.approval_status_cd,'PQH_GSP_NP') = 'PQH_GSP_NP'
380: and elc.in_pndg_wkflow_flag = 'Y'
381: and pil.per_in_ler_id = elc.per_in_ler_id
382: and pil.per_in_ler_stat_cd = 'STRTD'
448:
449: l_grade_cur_pop number;
450: l_quota_allowed number;
451: l_already_app number;
452: l_corp_name pqh_corps_definitions.name%type;
453: l_grade_name per_grades.name%type;
454: l_proc varchar2(60) := g_package||'check_quota 2';
455: l_return_status varchar2(1) := 'Y';
456: l_corp_id number;
457: Begin
458: hr_utility.set_location('Entering into '||l_proc, 10);
459:
460: select name, corps_definition_id into l_corp_name, l_corp_id
461: from pqh_corps_definitions
462: where ben_pgm_id = p_corp_id;
463:
464: if Quota_applicable(l_corp_id,p_effective_date) = 'Y' then
465: l_grade_cur_pop := population_grade(p_corp_id => l_corp_id ,
475: into l_already_app
476: from ben_elig_per_elctbl_chc elc,
477: ben_per_in_ler pil,
478: ben_pl_f pl,
479: pqh_corps_definitions corp
480: where elc.approval_status_cd = 'PQH_GSP_A'
481: and pil.per_in_ler_id = elc.per_in_ler_id
482: and pil.per_in_ler_stat_cd = 'STRTD'
483: and corp.corps_definition_id = l_corp_id