PER Worker Organization Movements

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