708 total views

In this article we will look into the difference between the PER_ALL_ASSIGNMENTS_M and _F tables which is the most confusing part for developers when they want to build queries based on the assignment information tables.

There are multiple tables for Assignment Information and we will look into details on which table stores what information:

PER_ALL_ASSIGNMENTS_M – This is the core table which stores all assignment related information.

PER_ALL_ASSIGNMENTS_F – This is a view based our of PER_ALL_ASSIGNMENTS_M table and picks up only the latest row for each effective date.
If multiple transactions are not there for a day, both _M and _F fetches same data for that day. If there are MCPD (Multiple Changes Per Day) then _F will fetch only one row whereas _M will fetch multiple rows.
EFFECTIVE_LATEST_CHANGE = ‘Y’ is the only condition added to the PER_ALL_ASSIGNMENTS_M to get _F view.

PER_ALL_ASSIGNMENTS_M_ – This is the audit table for core assignment table. If Auditing for Assignment business object is enabled, then data will get stored in this table for every update/delete/correction on assignment information. All Audit tables end with underscore ( _ ) character.

Hope you are now clear on the difference between these tables and ready to use then as per your requirement.

Join our fastest growing technology social groups to interact with experts in the Fusion space and get your queries resolved – Telegram group, Facebook Page and LinkedIn Group

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

This Post Has 3 Comments

  1. Pavan

    Hi Sricharan,
    If there is any changes done in correction mode the AUDIT_ACTION_TYPE_ column value will be ‘UPDATE’ in per_all_assignment_M_ table. How to differentiate/find between update and correction in table.

    Thanks,
    Pavan.

  2. Sricharan

    Hi Pavan,

    You have raised an interesting question and here is the answer for it..

    ,case when audit_action_type_ = ‘UPDATE’ then ‘Update’
    when audit_action_type_ = ‘HISTORY’ and creation_date = last_update_date then ‘Creation’
    when audit_action_type_ = ‘HISTORY’ and creation_date <> last_update_date then ‘History’
    when audit_action_type_ = ‘INSERT’ then ‘New’
    when audit_action_type_ = ‘DELETE’ then ‘Deleted’ end Audit_Action

    I will do another post on Audit Query to make it clear.
    Thanks,
    sricharan

  3. Pavan

    Hi Sricharan,

    Thanks for your replay.
    May question is how to find correction records. Because even if you do correction that record is storing as UPDATE in Audit table.

    Thanks,
    Pavan.

Leave a Reply