PER Assignment Status

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Assignment Status Report
Application: Human Resources
Source: Assignment Status Report (XML)
Short Name: PERRPRAS_XML
DB package: PER_PERRPRAS_XMLP_PKG
Run PER Assignment Status and other Oracle EBS reports with Blitz Report™ on our demo environment
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
PER_DATES_STANDARD
 
Date
Organization Structure
 
LOV Oracle
Version
 
LOV Oracle
Parent Organization
 
LOV Oracle
Group
 
Char
Job
 
LOV Oracle
Position
 
LOV Oracle
Grade
 
LOV Oracle
Payroll
 
LOV Oracle
Primary
 
LOV Oracle
Person Type
 
LOV Oracle
Assignment Status 1
 
LOV Oracle
Assignment Status 2
 
LOV Oracle
Assignment Status 3
 
LOV Oracle
Assignment Status 4
 
LOV Oracle
Blitz Report™