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.
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)
Suggested reading HCM Cloud ATOM Feeds
References : Oracle doc on HCM Tables