PER Assignment Status

select p.full_name        name
,      p.employee_number  employee_number
,      p.applicant_number applicant_number
,      m.meaning          manager_flag
,      pf.meaning          primary_flag
,      o.name             organization
,      l.location_code    location
,      r.name             recruitment_activity
,      v.name             vacancy
,      nvl(btl.user_status,stl.user_status) status
,      a.assignment_status_type_id
,      a.assignment_id
,      a.effective_start_date
,      a.effective_end_date
,      a.assignment_type
,      a.organization_id
,      decode(a.assignment_type,'A',1,0) count_applicants
,      decode(a.assignment_type,'E',1,0) count_employees
,      decode(nvl(b.active_flag,s.active_flag),'N','*',NULL) active_status_flag,
	PER_PERRPRAS_XMLP_PKG.c_status_end_dateformula(a.assignment_id, a.assignment_status_type_id) C_STATUS_END_DATE,
	PER_PERRPRAS_XMLP_PKG.c_status_start_dateformula(a.assignment_id, a.assignment_status_type_id) C_STATUS_START_DATE
from   per_assignments_f a
,      per_people_f p
,      hr_organization_units o
,      pay_people_groups g
,      per_vacancies v
,      per_recruitment_activities r
,      hr_lookups m
,      hr_lookups pf
,      hr_locations l
,      per_assignment_status_types s
,      per_assignment_status_types_tl stl
,      per_ass_status_type_amends b
,      per_ass_status_type_amends_tl btl
where ( (a.assignment_type || '' ='E'
              and NVL(:P_PRIMARY_FLAG,a.primary_flag) = a.primary_flag)
              or a.assignment_type || '' != 'E' )
&P_JOB_MATCHING
&P_POSITION_MATCHING
&P_GRADE_MATCHING
&P_PAYROLL_MATCHING
and    :C_SESSION_DATE between a.effective_start_date and a.effective_end_date
and    p.person_id = a.person_id
and    :C_SESSION_DATE between p.effective_start_date and p.effective_end_date
and (:P_PEOPLE_GROUP_ID is null or
     :P_PEOPLE_GROUP_ID is not null &P_MATCHING_CRITERIA)
and    g.id_flex_num(+) = :P_PEOPLE_GROUP_FLEX_ID
and    g.people_group_id(+) = a.people_group_id
&P_STATUS_MATCHING_1
&P_ORG_MATCHING
and    m.lookup_type(+) = 'YES_NO'
and    m.lookup_code(+) = a.manager_flag
and    pf.lookup_type(+) = 'YES_NO'
and    pf.lookup_code(+) = a.primary_flag
and    o.organization_id = a.organization_id
and    a.location_id = l.location_id(+)
and    a.recruitment_activity_id = r.recruitment_activity_id(+)
and    a.vacancy_id = v.vacancy_id(+)
and    a.assignment_status_type_id = s.assignment_status_type_id
and    b.assignment_status_type_id(+) = a.assignment_status_type_id
and    b.business_group_id(+) = a.business_group_id
and    s.assignment_status_type_id = stl.assignment_status_type_id
and    b.ass_status_type_amend_id = btl.ass_status_type_amend_id(+)
and    decode(btl.ass_status_type_amend_id, NULL, '1', btl.language)
           = decode(btl.ass_status_type_amend_id, NULL, '1', userenv('LANG'))
and    stl.language = userenv('LANG')
order by NVL(p.order_name,p.full_name), assignment_sequence,
                assignment_number

select p.full_name name , p.employee_number employee_number , p.applicant_number applicant_number , m.meaning manager_flag , pf.meaning primary_flag , o.name organization , l.location_code location , r.name recruitment_activity , v.name vacancy , nvl(btl.user_status,stl.user_status) status , a.assignment_status_type_id , a.assignment_id , a.effective_start_date , a.effective_end_date , a.assignment_type , a.organization_id , decode(a.assignment_type,'A',1,0) count_applicants , decode(a.assignment_type,'E',1,0) count_employees , decode(nvl(b.active_flag,s.active_flag),'N','*',NULL) active_status_flag, PER_PERRPRAS_XMLP_PKG.c_status_end_dateformula(a.assignment_id, a.assignment_status_type_id) C_STATUS_END_DATE, PER_PERRPRAS_XMLP_PKG.c_status_start_dateformula(a.assignment_id, a.assignment_status_type_id) C_STATUS_START_DATE from per_assignments_f a , per_people_f p , hr_organization_units o , pay_people_groups g , per_vacancies v , per_recruitment_activities r , hr_lookups m , hr_lookups pf , hr_locations l , per_assignment_status_types s , per_assignment_status_types_tl stl , per_ass_status_type_amends b , per_ass_status_type_amends_tl btl where ( (a.assignment_type || '' ='E' and NVL(:P_PRIMARY_FLAG,a.primary_flag) = a.primary_flag) or a.assignment_type || '' != 'E' ) &P_JOB_MATCHING &P_POSITION_MATCHING &P_GRADE_MATCHING &P_PAYROLL_MATCHING and :C_SESSION_DATE between a.effective_start_date and a.effective_end_date and p.person_id = a.person_id and :C_SESSION_DATE between p.effective_start_date and p.effective_end_date and (:P_PEOPLE_GROUP_ID is null or :P_PEOPLE_GROUP_ID is not null &P_MATCHING_CRITERIA) and g.id_flex_num(+) = :P_PEOPLE_GROUP_FLEX_ID and g.people_group_id(+) = a.people_group_id &P_STATUS_MATCHING_1 &P_ORG_MATCHING and m.lookup_type(+) = 'YES_NO' and m.lookup_code(+) = a.manager_flag and pf.lookup_type(+) = 'YES_NO' and pf.lookup_code(+) = a.primary_flag and o.organization_id = a.organization_id and a.location_id = l.location_id(+) and a.recruitment_activity_id = r.recruitment_activity_id(+) and a.vacancy_id = v.vacancy_id(+) and a.assignment_status_type_id = s.assignment_status_type_id and b.assignment_status_type_id(+) = a.assignment_status_type_id and b.business_group_id(+) = a.business_group_id and s.assignment_status_type_id = stl.assignment_status_type_id and b.ass_status_type_amend_id = btl.ass_status_type_amend_id(+) and decode(btl.ass_status_type_amend_id, NULL, '1', btl.language) = decode(btl.ass_status_type_amend_id, NULL, '1', userenv('LANG')) and stl.language = userenv('LANG') order by NVL(p.order_name,p.full_name), assignment_sequence, assignment_number