PER Worker Organization Movements

Description
Categories: BI Publisher, Human Resources
Application: Human Resources
Source: Worker Organization Movements Report (XML)
Short Name: PAYRPEMV_HR_XML
DB package: PER_PAYRPEMV_XMLP_PKG
select distinct
       paf.assignment_type        new_hire_asg_type
,      paf.assignment_id
,      paf.effective_start_date
,      paf.effective_end_date
,      ppos.date_start            date_start
,      ppf.full_name
,      paf.assignment_number
,      hou.name org
,      gdt.name grade
,      jbt.name job
,      pp.name position
,      hltl.location_code
,      hrl.meaning    new_hire_reason
,      null               new_hire_supplier_name
,      null               new_hire_supplier_site,
	PER_PAYRPEMV_XMLP_PKG.cf_control_total_newhireformul(paf.assignment_type, :ASSIGNMENT_TYPE) CF_control_total_newhire
from   per_assignments_f      paf
,      per_periods_of_service ppos
,      per_people_f           ppf
,      hr_organization_units  hou
,      per_grades_tl      gdt
,      per_jobs_tl           jbt
,      per_positions          pp
,      hr_locations_no_join     hl
,      hr_locations_all_tl           hltl
,      hr_leg_lookups hrl
where  hl.location_id = hltl.location_id (+)
and decode(hltl.location_id,null,'1',hltl.language) =
	decode(hltl.location_id,null,'1',userenv('LANG'))
and paf.period_of_service_id = ppos.period_of_service_id(+)
and paf.person_id            = ppf.person_id
and ppf.effective_end_date = (select min(ppf3.effective_end_date)
                               from per_people_f ppf3
                               where ppf3.person_id = paf.person_id
                                 and ppf3.effective_end_date >= sysdate
                               group by ppf3.person_id)
and    paf.organization_id      = hou.organization_id
and    paf.grade_id             = gdt.grade_id(+)
and    gdt.language(+)          = userenv('LANG')
and    paf.job_id               = jbt.job_id(+)
and    jbt.language(+)          = userenv('LANG')
and    paf.position_id          = pp.position_id(+)
and    paf.location_id          = hl.location_id(+)
and    paf.assignment_type = 'E'
and    :p_worker_type in ('E','B')
and    upper(:p_employee_detail) != 'S'
and   hrl.lookup_type = 'NEW_HIRE_REASON'
and   hrl.lookup_code = 'NEW_STARTER'
/* added as fix:*/
 and paf.assignment_id=:new_hires
 and paf.effective_start_date=:effective_start_date7
 and paf.effective_end_date=:effective_end_date7
 /* fix ends */
UNION ALL
select distinct
       paf.assignment_type      new_hire_asg_type
,      paf.assignment_id
,      paf.effective_start_date
,      paf.effective_end_date
,      ppp.date_start
,      ppf.full_name
,      paf.assignment_number
,      hou.name org
,      gdt.name grade
,      jbt.name job
,      pp.name position
,      hltl.location_code
,      hrl.meaning          new_hire_reason
,      POV.VENDOR_NAME              asg_supplier_name
,      POVS.VENDOR_SITE_CODE        asg_supplier_site,
	PER_PAYRPEMV_XMLP_PKG.cf_control_total_newhireformul(paf.assignment_type, :ASSIGNMENT_TYPE) CF_control_total_newhire
from   per_assignments_f        paf
,      per_periods_of_placement ppp
,      per_people_f             ppf
,      hr_organization_units    hou
,      per_grades_tl            gdt
,      per_jobs_tl              jbt
,      per_positions            pp
,      hr_locations_no_join     hl
,      hr_locations_all_tl      hltl
,      PO_VENDORS               POV
,      PO_VENDOR_SITES_ALL      POVS
,      hr_leg_lookups hrl
where  hl.location_id = hltl.location_id (+)
and decode(hltl.location_id,null,'1',hltl.language) =
	decode(hltl.location_id,null,'1',userenv('LANG'))
and paf.period_of_placement_date_start = ppp.date_start(+)
and paf.person_id                = ppp.person_id(+)
and paf.person_id            = ppf.person_id
and paf.assignment_type = 'C'
and ppf.effective_end_date = (select min(ppf3.effective_end_date)
                               from per_people_f ppf3
                               where ppf3.person_id = paf.person_id
                                 and ppf3.effective_end_date >= sysdate
                               group by ppf3.person_id)
and    paf.organization_id      = hou.organization_id
and    paf.grade_id             = gdt.grade_id(+)
and    gdt.language(+)          = userenv('LANG')
and    paf.job_id               = jbt.job_id(+)
and    jbt.language(+)          = userenv('LANG')
and    paf.position_id          = pp.position_id(+)
and    paf.location_id          = hl.location_id(+)
AND    paf.VENDOR_ID            = POV.VENDOR_ID (+)
AND    paf.VENDOR_SITE_ID       = POVS.VENDOR_SITE_ID (+)
and    :p_worker_type in ('C','B')
and    upper(:p_employee_detail) != 'S'
and   hrl.lookup_type = 'NEW_HIRE_REASON'
and   hrl.lookup_code = 'NEW_STARTER'
 and paf.assignment_id=:new_hires
 and paf.effective_start_date=:effective_start_date7
 and paf.effective_end_date=:effective_end_date7
Parameter Name SQL text Validation
Business Group ID
 
Number
Worker Detail
 
LOV Oracle
Worker Type
 
LOV Oracle
Date To
 
Date
Date From
 
Date
Parent Organization
 
LOV Oracle
Version
 
LOV Oracle
Organization Structure
 
LOV Oracle