PER Tax Batch Update

select tax.element_entry_id  element_entry_id
          ,tax.effective_start_date  effective_start_date
          ,per.full_name
          ,per.employee_number
          ,org.name
          ,asg.assignment_number
          ,asg.assignment_id,
	PER_PERDETBU_XMLP_PKG.cf_old_tax_classformula(asg.assignment_id, tax.element_entry_id) CF_OLD_TAX_CLASS,
	PER_PERDETBU_XMLP_PKG.cf_old_nocformula(asg.assignment_id, tax.element_entry_id) CF_old_noc,
	PER_PERDETBU_XMLP_PKG.cf_old_ytfiformula(asg.assignment_id, tax.element_entry_id) CF_old_ytfi,
	PER_PERDETBU_XMLP_PKG.cf_old_aytiformula(asg.assignment_id, tax.element_entry_id) CF_old_ayti,
	PER_PERDETBU_XMLP_PKG.cf_old_amtiformula(asg.assignment_id, tax.element_entry_id) CF_old_amti,
	PER_PERDETBU_XMLP_PKG.cf_old_mtfiformula(asg.assignment_id, tax.element_entry_id) CF_old_mtfi,
	PER_PERDETBU_XMLP_PKG.cf_new_tax_classformula(asg.assignment_id, tax.element_entry_id) CF_NEW_TAX_CLASS,
	PER_PERDETBU_XMLP_PKG.cf_new_nocformula(asg.assignment_id, tax.element_entry_id) CF_new_noc,
	PER_PERDETBU_XMLP_PKG.cf_new_ytfiformula(asg.assignment_id, tax.element_entry_id) CF_new_ytfi,
	PER_PERDETBU_XMLP_PKG.cf_new_aytiformula(asg.assignment_id, tax.element_entry_id) CF_new_ayti,
	PER_PERDETBU_XMLP_PKG.cf_new_amtiformula(asg.assignment_id, tax.element_entry_id) CF_new_amti,
	PER_PERDETBU_XMLP_PKG.cf_new_mtfiformula(asg.assignment_id, tax.element_entry_id) CF_new_mtfi
from  per_all_assignments_f    asg
         ,per_all_people_f           per
         ,hr_all_organization_units org
         ,hr_de_tax_information_v   tax
where :p_action   ='UR'
and   tax.process_id    = to_char(:p_conc_request_id)
 and  tax.effective_start_date = :p_date_from
 and  asg.assignment_id  = tax.assignment_id
 and  per.person_id  = asg.person_id
 and org.organization_id  = asg.organization_id
 and  tax.effective_start_date  BETWEEN asg.effective_start_date  AND asg.effective_end_date
and  tax.effective_start_date between per.effective_start_date  AND per.effective_end_date
 union all
select  to_number(null) element_entry_id,
           to_date(NULL) effective_start_date,
           per.full_name,
          per.employee_number,
         org.name,
         asg.assignment_number,
         asg.assignment_id,
	PER_PERDETBU_XMLP_PKG.cf_old_tax_classformula(asg.assignment_id, to_number(null)) CF_OLD_TAX_CLASS,
	PER_PERDETBU_XMLP_PKG.cf_old_nocformula(asg.assignment_id, to_number(null)) CF_old_noc,
	PER_PERDETBU_XMLP_PKG.cf_old_ytfiformula(asg.assignment_id, to_number(null)) CF_old_ytfi,
	PER_PERDETBU_XMLP_PKG.cf_old_aytiformula(asg.assignment_id, to_number(null)) CF_old_ayti,
	PER_PERDETBU_XMLP_PKG.cf_old_amtiformula(asg.assignment_id, to_number(null)) CF_old_amti,
	PER_PERDETBU_XMLP_PKG.cf_old_mtfiformula(asg.assignment_id, to_number(null)) CF_old_mtfi,
	PER_PERDETBU_XMLP_PKG.cf_new_tax_classformula(asg.assignment_id, to_number(null)) CF_NEW_TAX_CLASS,
	PER_PERDETBU_XMLP_PKG.cf_new_nocformula(asg.assignment_id, to_number(null)) CF_new_noc,
	PER_PERDETBU_XMLP_PKG.cf_new_ytfiformula(asg.assignment_id, to_number(null)) CF_new_ytfi,
	PER_PERDETBU_XMLP_PKG.cf_new_aytiformula(asg.assignment_id, to_number(null)) CF_new_ayti,
	PER_PERDETBU_XMLP_PKG.cf_new_amtiformula(asg.assignment_id, to_number(null)) CF_new_amti,
	PER_PERDETBU_XMLP_PKG.cf_new_mtfiformula(asg.assignment_id, to_number(null)) CF_new_mtfi
FROM  per_all_assignments_f  asg,
            per_all_people_f     per,
           hr_all_organization_units  org,
           hr_de_asg_tax_batch_upd_v   abu
WHERE :p_action   ='R'
and      abu.process_id = :p_conc_request_id
 and    asg.assignment_id  = abu.assignment_id
 and  per.person_id    = asg.person_id
 and  org.organization_id  = asg.organization_id
  and   :p_date_from  BETWEEN   asg.effective_start_date   and asg.effective_end_date
  and  :p_date_from   BETWEEN   per.effective_start_date   and per.effective_end_date