2 Key Difference between PER_ALL_ASSIGNMENTS_M and PER_ALL_ASSIGNMENTS_F in HCM Cloud

per_all_assignments_m
PER_ALL_ASSIGNMENTS_M is the core base table used to store assignment records of employee.
  • It stores two levels of the 3-Tier Model: Employment aka Placement Terms (Level 2) and Assignments (Level 3).
  • The assignment type is used to differentiate between these two levels
  • Differentiate among employee, contingent worker, applicants, and benefits assignments.
  • This is date-tracked and allows multiple changes in a day.
  • Primary key of this table is ASSIGNMENT_ID

PER_ALL_ASSIGNMENTS_M is a TABLE

PER_ALL_ASSIGNMENTS_F is a view based on PER_ALL_ASSIGNMENTS_M table. This view only shows person assignments whose latest change is effective.

SELECT *
FROM
  PER_ALL_ASSIGNMENTS_M paa
WHERE
  EFFECTIVE_LATEST_CHANGE = 'Y';
So the answer to question, when to use PER_ALL_ASSIGNMENTS_M  table is that if you want all the data use the base table. 

And,  If you want the effective latest change only use PER_ALL_ASSIGNMENTS_F

If there are changes to employee assignment in a day you will see multiple records in the PER_ALL_ASSIGNMENTS_M table.

If there are no changes you will see the same data in PER_ALL_ASSIGNMENTS_M and PER_ALL_ASSIGNMENTS_F table and view respectively.

per_all_assignments_m_ is another table which is the audit table. It stores the history of changes to assignment objects when auditing is enabled. (as a convention all table ending with _ are audit tables)

SQL query to fetch Employee in fusion hcm

The assignment table is joined to per_all_people_f the master table for employee records and org tables as per below sample query.

SELECT perf.person_number, hruf.name businessunit
  FROM hr_org_unit_classifications_f hrc,
       hr_all_organization_units_f hrall,
       hr_organization_units_f_tl hruf,
       per_all_assignments_m pasm,
       per_all_people_f perf
 WHERE hrall.organization_id = hrc.organization_id
       AND hrall.organization_id = hruf.organization_id
       AND hrall.effective_start_date BETWEEN hrc.effective_start_date
       AND hrc.effective_end_date
       AND hruf.language = 'US'
       AND hruf.effective_start_date = hrall.effective_start_date
       AND hruf.effective_end_date = hrall.effective_end_date
       AND SYSDATE BETWEEN hruf.effective_start_date
       AND hruf.effective_end_date
       AND hruf.organization_id = pasm.business_unit_id
       AND pasm.person_id = perf.person_id
       AND pasm.assignment_type = 'E'
       AND pasm.effective_latest_change = 'Y'
       AND SYSDATE BETWEEN pasm.effective_start_date
       AND pasm.effective_end_date
       AND perf.person_number = NVL (:personnumber, perf.person_number)

FAQs

Suggested reading HCM Cloud ATOM Feeds

References : Oracle doc on HCM Tables

wpsadmin
We will be happy to hear your thoughts

Leave a reply

Cloud Technologies Blog
Logo
Enable registration in settings - general
Compare items
  • Total (0)
Compare
0
Ads Blocker Image Powered by Code Help Pro

Ads Blocker Detected!!!

We have detected that you are using extensions to block ads. Please support us by disabling these ads blocker.

Powered By
Best Wordpress Adblock Detecting Plugin | CHP Adblock
Shopping cart