PER Job and Position Skills Matching

select o.name organization
,      nvl(s.user_status,t.user_status) status
,      a.effective_start_date date_from
,      a.effective_end_date date_to
,      a.assignment_id
,      a.person_id,
	PER_PERRPRMS_XMLP_PKG.c_date_toformula(a.effective_end_date) C_DATE_TO
--from
--,      a.effective_end_date date_to
--,      a.assignment_id
--,      a.person_id
from   per_assignments_f a
,      hr_all_organization_units o
,      per_assignment_status_types t
,      per_ass_status_type_amends s
where  :P_SESSION_DATE between
       a.effective_start_date and a.effective_end_date
and    a.organization_id = o.organization_id
and    a.assignment_status_type_id = t.assignment_status_type_id
and    s.assignment_status_type_id(+) = t.assignment_status_type_id
and    s.business_group_id(+) = :P_BUSINESS_GROUP_ID
and    nvl(t.business_group_id,:P_BUSINESS_GROUP_ID)
                              = :P_BUSINESS_GROUP_ID
and    nvl(t.legislation_code,:P_LEGISLATION_CODE)
                              = :P_LEGISLATION_CODE
and    nvl(s.active_flag,t.active_flag) = 'Y'
and    nvl(s.default_flag,t.default_flag) = 'Y'
and a.person_id=:person_id1