[Home] [Help]
180: ) RETURN VARCHAR2 IS
181:
182: CURSOR c_srcInfo (p_cid IN NUMBER, p_pid IN NUMBER) IS
183: SELECT 4 rank, hr_general.decode_lookup('STRUCTURE_TYPE','POS')||'#'||proficiency_level_id||'#'||high_proficiency_level_id src
184: FROM per_competence_elements ce, per_all_assignments_f paf
185: WHERE ce.type = 'REQUIREMENT'
186: AND trunc(sysdate) between nvl(ce.effective_date_from, sysdate) and nvl(ce.effective_date_to, sysdate)
187: AND ce.position_id = paf.position_id
188: AND paf.primary_flag = 'Y'
192: AND ce.competence_id = p_cid
193: AND paf.person_id = p_pid
194: UNION ALL
195: SELECT 3 rank, hr_general.decode_lookup('STRUCTURE_TYPE','JOB')||'#'||proficiency_level_id||'#'||high_proficiency_level_id src
196: FROM per_competence_elements ce, per_all_assignments_f paf
197: WHERE ce.type = 'REQUIREMENT'
198: AND trunc(sysdate) between nvl(ce.effective_date_from, sysdate) and nvl(ce.effective_date_to, sysdate)
199: AND ce.job_id = paf.job_id
200: AND paf.primary_flag = 'Y'
204: AND ce.competence_id = p_cid
205: AND paf.person_id = p_pid
206: UNION ALL
207: SELECT 2 rank, hr_general.decode_lookup('STRUCTURE_TYPE','ORG')||'#'||proficiency_level_id||'#'||high_proficiency_level_id src
208: FROM per_competence_elements ce, per_all_assignments_f paf
209: WHERE ce.type = 'REQUIREMENT'
210: AND trunc(sysdate) between ce.effective_date_from and nvl(ce.effective_date_to, sysdate)
211: AND ce.organization_id = paf.organization_id
212: AND paf.primary_flag = 'Y'
216: AND ce.competence_id = p_cid
217: AND paf.person_id = p_pid
218: UNION ALL
219: SELECT 1 rank, hr_general.decode_lookup('STRUCTURE_TYPE','BUS')||'#'||proficiency_level_id||'#'||high_proficiency_level_id src
220: FROM per_competence_elements ce, per_all_assignments_f paf
221: WHERE ce.type = 'REQUIREMENT'
222: AND trunc(sysdate) between ce.effective_date_from and nvl(ce.effective_date_to, sysdate)
223: AND ce.enterprise_id = paf.business_group_id
224: AND paf.primary_flag = 'Y'
859: p_effective_date IN date)
860: return number
861: is
862: CURSOR csr_apl_asgs_count is
863: select assignment_id from per_all_assignments_f
864: where person_id = p_person_id and assignment_type = 'A' and
865: p_effective_date between effective_start_date and effective_end_date;
866: l_apl_asgs_count INTEGER := 0;
867: begin
976: itemtype => itemtype,
977: itemkey => itemkey,
978: aname => 'CURRENT_EFFECTIVE_DATE');
979:
980: select primary_flag into dummy from per_all_assignments_f
981: where assignment_id=l_number_value and l_date_value between effective_start_date
982: and effective_end_date;
983:
984: if dummy = 'Y'
1009: return date is
1010: l_asg_term_date date;
1011: begin
1012: select min(asg.EFFECTIVE_start_DATE) into l_asg_term_date
1013: from per_all_assignments_f asg ,per_assignment_status_types ast
1014: where asg.assignment_id = p_assignment_id
1015: and ast.assignment_status_type_id = asg.assignment_status_type_id
1016: and ast.per_system_status = 'TERM_ASSIGN';
1017:
1016: and ast.per_system_status = 'TERM_ASSIGN';
1017:
1018: if l_asg_term_date is null then
1019: select max(EFFECTIVE_end_DATE) into l_asg_term_date
1020: from per_all_assignments_f where assignment_id = p_assignment_id;
1021: if l_asg_term_date = hr_api.g_eot then
1022: l_asg_term_date := null;
1023: end if;
1024: end if;
2049: END;
2050: BEGIN
2051: SELECT application_id
2052: INTO l_application_id
2053: FROM per_all_assignments_f
2054: WHERE assignment_id = l_assignment_id;
2055: /*AND sysdate
2056: BETWEEN effective_start_date
2057: AND effective_end_date;*/
2380:
2381: if(p_rptg_grp_id is not null and p_plan_id is null) then
2382: begin
2383: hr_utility.set_location('Before selecting job_id from paf', 30);
2384: select job_id into l_job_id from per_all_assignments_f paf
2385: where assignment_id = p_rptg_grp_id
2386: and sysdate between paf.effective_start_date and paf.effective_end_date;
2387: exception
2388: when others then
2419: return varchar2
2420: is
2421:
2422: --l_position_id number(10):=-1;
2423: l_position_id per_all_assignments_f.position_id%type := -1; --14592612
2424: --l_position_name varchar2(100) := null;
2425: l_position_name hr_all_positions_f_tl.name%type := ''; --14592612
2426: begin
2427:
2427:
2428: if(p_rptg_grp_id is not null and p_plan_id is null) then
2429: begin
2430:
2431: select position_id into l_position_id from per_all_assignments_f paf
2432: where assignment_id = p_rptg_grp_id
2433: and sysdate between paf.effective_start_date and paf.effective_end_date;
2434:
2435: exception
2455: return varchar2
2456: is
2457:
2458: --l_grade_id number(10):=-1;
2459: l_grade_id per_all_assignments_f.grade_id%type := -1; --14592612
2460: --l_grade_name varchar2(100) := null;
2461: l_grade_name per_grades_tl.name%type := ''; --14592612
2462: begin
2463:
2463:
2464: if(p_rptg_grp_id is not null and p_plan_id is null) then
2465: begin
2466:
2467: select grade_id into l_grade_id from per_all_assignments_f paf
2468: where assignment_id = p_rptg_grp_id
2469: and sysdate between paf.effective_start_date and paf.effective_end_date;
2470:
2471: exception
2492: return varchar2
2493: is
2494:
2495: --l_org_id number(10):=-1;
2496: l_org_id per_all_assignments_f.ORGANIZATION_ID%type := -1; --14592612
2497: --l_org_name varchar2(100) := null;
2498: l_org_name hr_all_organization_units_tl.name%type := ''; --14592612
2499: begin
2500:
2500:
2501: if(p_rptg_grp_id is not null and p_plan_id is null) then
2502: begin
2503:
2504: select ORGANIZATION_ID into l_org_id from per_all_assignments_f paf
2505: where assignment_id = p_rptg_grp_id
2506: and sysdate between paf.effective_start_date and paf.effective_end_date;
2507:
2508: exception
2528: return varchar2
2529: is
2530:
2531: --l_loc_id number(10):=-1;
2532: l_loc_id per_all_assignments_f.Location_ID%type := -1; --14592612
2533: --l_loc_name varchar2(100) := null;
2534: l_loc_name hr_locations_all_tl.location_code%type := ''; --14592612
2535: begin
2536: -- hr_utility.trace_on(null,'ORCL');
2536: -- hr_utility.trace_on(null,'ORCL');
2537: if(p_rptg_grp_id is not null and p_plan_id is null) then
2538: begin
2539:
2540: select Location_ID into l_loc_id from per_all_assignments_f paf
2541: where assignment_id = p_rptg_grp_id
2542: and sysdate between paf.effective_start_date and paf.effective_end_date;
2543:
2544: exception
2571:
2572: if(p_rptg_grp_id is not null and p_plan_id is null) then
2573: begin
2574:
2575: select distinct(supervisor_id) into l_manager_id from per_all_assignments_f paf where paf.assignment_id = p_rptg_grp_id
2576: and sysdate between paf.effective_start_date and paf.effective_end_date;
2577:
2578: exception
2579: when others then
2583:
2584: else
2585: begin
2586:
2587: select distinct(supervisor_id) into l_manager_id from per_all_assignments_f paf where paf.assignment_id = p_assignment_id
2588: and sysdate between paf.effective_start_date and paf.effective_end_date;
2589:
2590: exception
2591: when others then