الفنكشن دى عملتها وبترجع من غير داتا رغم ان انا جربت الحل بتاعكم
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;ارجوكم المساعدة الموضوع مهم جدا