الفنكشن دى عملتها وبترجع من غير داتا رغم ان انا جربت الحل بتاعكم
http://www.araboug.o...showtopic=48944
function get_job(p_person_id number ) return varchar2
is
cursor s is
select distinct
nvl(jbt.name,'')job
from per_all_assignments_f asg,
per_assignment_status_types ast,
per_assignment_status_types_tl astl,
hr_all_organization_units org,
hr_all_organization_units_tl orgtl,
pay_all_payrolls_f ppa,
per_jobs_tl jbt,
per_grades_tl gdt,
pay_people_groups ppg,
/* hr_positions pos,*/
hr_locations loc,
per_all_people_f peo,
hr_lookups hrl,
hr_lookups hrl2,
hr_lookups hrl3,
hr_lookups hrl4
where asg.assignment_status_type_id = ast.assignment_status_type_id
and ast.assignment_status_type_id = astl.assignment_status_type_id
and astl.language = userenv('LANG')
and asg.organization_id = org.organization_id
and org.organization_id = orgtl.organization_id
and orgtl.language = userenv('LANG')
and asg.payroll_id = ppa.payroll_id (+)
and asg.job_id = jbt.job_id (+)
and jbt.language(+) = userenv('LANG')
and asg.grade_id = gdt.grade_id (+)
and gdt.language(+) = userenv('LANG')
and asg.people_group_id = ppg.people_group_id (+)
/* and asg.position_id = pos.position_id (+)*/
and asg.location_id = loc.location_id (+)
and asg.supervisor_id = peo.person_id (+)
--
and hrl.lookup_type (+) = 'FREQUENCY'
and hrl.lookup_code (+) = asg.frequency
and hrl2.lookup_type (+) = 'QUALIFYING_UNITS'
and hrl2.lookup_code (+) = asg.probation_unit
and hrl3.lookup_type (+) = 'YES_NO'
and hrl3.lookup_code (+) = asg.manager_flag
and hrl4.lookup_type (+) = 'YES_NO'
and hrl4.lookup_code (+) = asg.primary_flag
and asg.person_id= p_person_id;
j varchar2(300);
begin
open s ;
fetch s into j;
if s%notfound
then
j:='no';
end if;
close s;
ارجوكم المساعدة الموضوع مهم جدا
أعلى
