• Post category:BI Publisher
  • Post comments:3 Comments
  • Post last modified:August 7, 2020
  • Reading time:2 mins read
You are currently viewing Assignment tables PER_ALL_ASSIGNMENTS_M and _F differences
Assignment tables PER_ALL_ASSIGNMENTS_M and _F differences

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.

  • 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.

  • 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

  • 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.

  • In visible box by plugintheme