Clearer link between per_all_assignments_m and payroll _run_results

Karen Costakes

Description

Many times worker assignment data needs to be linked to payroll run results information.

The assignment for the payroll run results can be identified in a variety of ways (via max logic and person_id) but a cleaner link between per_all_assignments_m and pay_run_results would be incredibly helpful for custom bi publisher reports.

In EBS there used to be a table that would connect the assignment to the payroll run results, but in cloud there is not.

Below is a portion of a log that was copied from an OTBI report built in Payroll Run Results. 

AND asg.PERSON_ID                                        = PayrollRelationshipPEO.PERSON_ID

AND (asg.assignment_status_type <> 'INACTIVE'
OR (asg.assignment_status_type   = 'INACTIVE'
AND NOT EXISTS
  (SELECT NULL
  FROM per_all_assignments_f aaa
  WHERE aaa.person_id               = asg.person_id
  AND aaa.WORK_TERMS_ASSIGNMENT_ID IS NOT NULL
  AND aaa.PRIMARY_FLAG              = 'Y'
  AND aaa.assignment_id!=asg.assignment_id
  AND PayrollActionPEO.EFFECTIVE_DATE BETWEEN aaa.effective_start_Date AND aaa.effective_end_date
  AND (aaa.assignment_status_type <> 'INACTIVE'
  OR (aaa.assignment_status_type   = 'INACTIVE'
  AND asg.ASSIGNMENT_ID            < aaa.ASSIGNMENT_ID))

Use Case and Business Need

Many reports are needed linking job or assignment data to payroll processing information and although it is doable in both BI Publisher, OTBI, and HCM extracts, it could be cleaner in BI Publisher if there was a clearer link.  This would help with report build and potential report discrepancies.  

 

Original Idea Number: b4b1b632b7