PER Assignment Status

Description
Categories: BI Publisher, Human Resources
Application: Human Resources
Source: Assignment Status Report (XML)
Short Name: PERRPRAS_XML
DB package: PER_PERRPRAS_XMLP_PKG
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
Parameter Name SQL text Validation
Group Flex ID
 
Number
Business Group Id
 
Number
Assignment Status 4
 
LOV Oracle
Assignment Status 3
 
LOV Oracle
Assignment Status 2
 
LOV Oracle
Assignment Status 1
 
LOV Oracle
Person Type
 
LOV Oracle
Primary
 
LOV Oracle
Payroll
 
LOV Oracle
Grade
 
LOV Oracle
Position
 
LOV Oracle
Job
 
LOV Oracle
Group
 
Parent Organization
 
LOV Oracle
Version
 
LOV Oracle
Organization Structure
 
LOV Oracle
PER_DATES_STANDARD
 
Date