assignment audit table

Database Design for Audit Logging

Gustavo du Mortier is a functional and data analyst at MasterSoft, an Argentinean software company specializing in ERP and healthcare solutions. He’s written many books and articles on different aspects of programming and databases. In his spare time, he plays guitar and helps his two sons build and enhance their gaming computers.

• database design

Thinking of a database design for audit logging? Remember what happened to Hansel and Gretel: they thought leaving a simple trail of breadcrumbs was a good way to trace their steps.

When we design a data model, we are trained to apply the philosophy that now is all that exists . For example, if we design a schema to store prices for a product catalog, we may think that the database only needs to tell us the price of each product at the present moment. But if we wanted to know if the prices were modified and, if so, when and how those modifications occurred, we would be in trouble. Of course, we could design the database specifically to keep a chronological record of changes – commonly known as an audit trail or audit log.

Audit logging allows a database to have a ‘memory’ of past events. Continuing with the price list example, a proper audit log will allow the database to tell us exactly when a price was updated, what the price was before it was updated, who updated it, and from where.

Database Audit Logging Solutions

It would be great if the database could keep a snapshot of its state for every change that occurs in its data. This way, you could go back to any point in time and see how the data was at that precise moment just as if you were rewinding a movie. But that way of generating audit logging is obviously impossible; the resulting volume of information and the time it would take to generate the logs would be too high.

Audit logging strategies are based on generating audit trails only for data that can be deleted or modified. Any alteration in them must be audited to roll back changes, query the data in history tables, or track suspicious activity.

There are several popular audit logging techniques, but none of them serve every purpose. The most effective ones are often expensive, resource intensive, or performance degrading. Others are cheaper in terms of resources but are either incomplete, cumbersome to maintain, or require a sacrifice in design quality. Which strategy you choose will depend on the application requirements and the performance limits, resources, and design principles you need to respect.

Out-of-the-Box Logging Solutions

These audit logging solutions work by intercepting all commands sent to the database and generating a change log in a separate repository. Such programs offer multiple configuration and reporting options to track user actions . They can log all actions and queries sent to a database, even when they come from users with the highest privileges. These tools are optimized to minimize performance impact, but this often comes at a monetary cost.

The price of dedicated audit trail solutions can be justified if you’re handling highly sensitive information (such as medical records) where any alteration of the data must be perfectly monitored and auditable and the audit trail must be unalterable. But when audit trail requirements are not as stringent, the cost of a dedicated logging solution can be excessive.

The native monitoring tools offered by relational database systems (RDBMSs) can also be used to generate audit trails. Customization options allow filtering which events are recorded, so as not to generate unnecessary information or overload the database engine with logging operations that will not be used later. The logs generated in this way allow detailed tracking of the operations executed on the tables. However, they are not useful for querying history tables, since they only record events.

The most economical option for maintaining an audit trail is to specifically design your database for audit logging. This technique is based on log tables that are populated by triggers or mechanisms specific to the application that updates the database. There is no universally accepted approach for audit logging database design, but there are several commonly used strategies, each of which has its pros and cons.

Database Audit Logging Design Techniques

Row versioning for audit logging in place.

One way to maintain an audit trail for a table is to add a field that indicates the version number of each record. Insertions into the table are saved with an initial version number. Any modifications or deletions actually become insertion operations, where new records are generated with the updated data and the version number is incremented by one. You can see an example of this audit logging in place design below:

Note: Table designs with embedded row versioning cannot be linked by foreign key relationships.

In addition to the version number, some extra fields should be added to the table to determine the origin and cause of each change made to a record:

• The date/time when the change was recorded.
• The user and application.
• The action performed (insert, update, delete), etc. For the audit trail to be effective, the table must only support insertions (updates and deletions should not be allowed). The table also necessarily requires a surrogate primary key, since any other combination of fields will be subject to repetition.

To access the updated table data through queries, you must create a view that returns only the latest version of each record. Then, you must replace the name of the table with the name of the view in all queries except those specifically intended to view the chronology of records.

This versioning option’s advantage is that it does not require using additional tables to generate the audit trail. Plus, only a few fields are added to the audited tables. But it has a huge disadvantage: it will force you to make some of the most common database design errors . These include not using referential integrity or natural primary keys when it is necessary to do so, making it impossible to apply the basic principles of entity-relationship diagram design. You can visit these useful resources on database design errors , so you’ll be warned on what other practices should be avoided.

Shadow Tables

Another audit trail option is to generate a shadow table for each table that needs to be audited. The shadow tables contain the same fields as the tables they audit, plus the specific audit fields (the same ones mentioned for the row versioning technique).

Shadow tables replicate the same fields as the tables they audit, plus the fields specific for auditing purposes.

To generate audit trails in shadow tables, the safest option is to create insert, update and delete triggers , that for each affected record in the original table generate a record in the audit table. The triggers should have access to all the audit information you need to record in the shadow table. You will have to use the database engine’s specific functionality to obtain data such as the current date and time, logged user, application name, and the location (network address or computer name) where the operation originated.

If using triggers is not an option, the logic to generate the audit trails should be part of the application stack, in a layer ideally located just before the data persistence layer, so that it can intercept all the operations directed towards the database.

This kind of log table should only allow record insertion; if they allow modifying or deleting, the audit trail would no longer fulfill its function. The tables also must use surrogate primary keys, as the dependencies and relationships of the original tables cannot be applied to them.

If the table for which you have created an audit trail has tables on which it depends, these should also have corresponding shadow tables. This is so that the audit trail is not orphaned if changes are made to the other tables.

Shadow tables are convenient because of their simplicity and because they do not affect the integrity of the data model; the audit trails remain in separate tables and are easy to query. The drawback is that the scheme is not flexible: any change in the structure of the main table must be reflected in the corresponding shadow table, which makes it difficult to maintain the model. In addition, if audit logging needs to be applied to a large number of tables, the number of shadow tables will also be high, making schema maintenance even harder.

Generic Tables for Audit Logging

A third option is to create generic tables for audit logs. Such tables allow the logging of any other table in the schema. Only two tables are required for this technique:

A header table that records:

• The date and time of the change.
• The name of the table.
• The key of the affected row.
• The user data.
• The type of operation performed.

A details table that records:

• The names of each affected field.
• The field value(s) before the modification.
• The field value(s) after the modification. (You may omit this if necessary, as it can be obtained by consulting the following record in the audit trail or the corresponding record in the audited table.)

The use of generic audit log tables places limits on the types of data that can be audited.

This audit logging strategy’s advantage is that it does not require any tables other than the two mentioned above. Also, records are stored in it only for the fields that are affected by an operation. This means that there is no need to replicate a whole row of a table when only one field is modified. In addition, this technique allows you to keep a log of as many tables as you want – without cluttering the schema with a large number of additional tables.

The disadvantage is that the fields that store the values must be of a single type – and wide enough to store even the largest of the fields of the tables for which you want to generate an audit log. It is most common to use VARCHAR-type fields that accept a large number of characters.

If, for example, you need to generate an audit log for a table that has one VARCHAR field of 8,000 characters, the field that stores the values in the audit table must also have 8,000 characters. This is true even if you just store one integer in that field. On the other hand, if your table has fields of complex data types, such as images, binary data, BLOBs, etc., you will need to serialize their contents so that they can be stored in the log tables’ VARCHAR fields.

Choose Your Database Audit Log Design Wisely

We have seen several alternatives for generating audit logging, but none of them is really optimal. You must adopt a logging strategy that does not substantially affect the performance of your database, does not make it grow excessively, and can meet your traceability requirements. If you only want to store logs for a few tables, shadow tables may be the most convenient option. If you want the flexibility to log any table, generic logging tables may be best.

Have you discovered another way to keep an audit log for your databases? Share it in the comments section below – your fellow database designers will be very grateful!

You may also like

The 9 most common database design errors, 19 online resources for learning about database design errors.

Our website uses cookies. By using this website, you agree to their use in accordance with the browser settings. You can modify your browser settings on your own. For more information see our Privacy Policy .

Audit and Prevent Unwanted SQL Server Table Changes

By: John Miner   |   Comments (2)   |   Related: > Auditing and Compliance

The process of classifying a company into an industry segment has been around since the 1950’s.  Wikipedia has listed several popular taxonomies that are in current use.  Some industries are more regulated and have stricter compliance regulations than others.  The business requirement to provide an audit trail to a compliance officer when a security issue arises is “table stakes” in most organizations.

SQL Server has had the ability to define an audit specification at the server or database level since 2008.  However, the actual changed data is not readily available for reversing the unwanted action.  In today’s complex team environments, certain key tables need to be protected against inserts, updates and deletes.  As a database administrator, how can we audit and prevent unwanted table changes? 

A design pattern using an audit schema, a central audit table and table triggers can be used to log all user actions against a group of tables. For larger databases, the central audit table can be transitioned to one audit table to one data table design.  Spreading the audit data across multiple tables will allow the system to scale.  Because this design pattern uses database objects that are core to every modern version of SQL Server, this design will work with both on premise and in-cloud databases.

Business Problem

The medical industry has to comply with the Health Insurance Portability and Accountability Act of 1996 (HIPAA).   If you are interested, detailed information can be found on the Department of Health and Human Services website . 

I am going to re-use the simple database schema for a fictitious urgent care center called “The Last Stop” .  Please see my prior article for details on how to setup this test database .  Our boss has asked us to showcase these new design patterns.  

Use existing Azure SQL database

I am going to use SQL Server Management Studio to manage my existing Azure SQL database.  Choose the database engine as the server type and enter the fully qualified name of the Azure SQL Server.  Pick SQL Server authentication as the security option and supply the login/password of the server administrator.  The image below shows a typical connect to server login window.

Right now, there is one custom user defined database.  Use the object explorer to drill into the hippa database and expand the tables list.  There are three tables in the active schema. 

The patient_info table contains patients that visited the clinic and the doctor_info table contains doctors that saw the patients.  The visit_info table has the overall medical readings taken during the visit as well as a medical diagnosis.

If we take a closer look at the user defined objects, we can see that each of the three tables have a surrogate primary key.  The visit_info table is used to join all tables together with foreign key relationships.

Please make sure that your test database is all ready for the rest of the article.  Since the build script can be deployed to either locally using on premise resources or remotely using in cloud resources, it is up to you on where you want to do your testing.

Securing Audit Data

I think user defined database schemas are under-utilized in the real world.  One great feature behind this security object is the ability to group like objects and assign permissions to the group. 

The above code creates a new schema named audit .  We can query the system catalog view named sys.schemas to see our three user defined schemas:  active, stage and audit.  See the screen shot below for details.

Containing a database user

The contained database user feature was introduced in SQL Server 2012.  All Azure SQL database users should be defined this way since they will not be dependent upon the master database.   This is especially important when active geo-replication is used for high availability.

The T-SQL code above creates a contained database user.  We can query the system catalog view named sys.database_principles to view our new user named hippa_user .

The image above shows the default schema is set to active .  This is important since we will only give this user access to that schema via a database role.

Granting access via database roles

Another rarely used feature in database design is a user defined database role .  Roles are great when you are using either standard or mixed security.  They serve the same purpose as an active directory group.  The code below creates a role named hippa_role .

The next step in assigning security is to grant permissions to the role.  I am going to give out CRUD (create, read, update and delete) rights to the user.  Of course, we can’t forget to add the user to the role.

The sys.database_role_members catalog view is a little more difficult to query than most.  It is a paired list of role and user ids.

The query above will return the role name and user name in the current database.  See the screen shot below for details.

Make a central audit table

It is time now to leverage the audit schema that we created previously.  The name of our audit table is log_table_changes .  Basic information such as change date, change type, change by, schema name and object name will be recorded. 

What makes this table interesting are the default constraints.  We are capturing the user name , application name , and host name via calls to system functions.  Last but not least, the changed record(s) are stored as an XML document.

The screen shot below shows the new table named log_table_changes .

This current database only has three tables.  Therefore, creating a table trigger by hand is not a problem.  What happens when the database has hundreds of tables.  In the next section, we will create a stored procedure named manage_table_triggers which will automate the process.

Managing table triggers

The actual code behind the stored procedure is quite large.  Therefore, I am more concerned about explaining the complete algorithm versus you understanding each line of code.  The manage_table_triggers stored procedure takes three parameters as input. 

The @target_schema_name parameter defaults to the active schema.  However, you can supply any schema name with one exception.  Creating a trigger on the log_table_changes table will cause a never ending cycle when the trigger fires. 

The @command_action parameter has two correct values.  The drop action removes all audit triggers from all tables in a given schema.  The create action clobbers the current audit trigger with a new one.

The @verbose_flag is used to optionally print the transact SQL statements that are being executed by the stored procedure.

Using a cursor to compose and execute dynamic transact SQL inside a maintenance procedure is an excepted practice.  This custom stored procedure assumes the number of tables to create an trigger on is limited.  The resulting DML after triggers use the inserted and deleted tables to capture and store the changed data as a XML document in the audit table.

The enclosed script creates the custom stored procedure and is provided as reference.  However, you do not need to understand the code to start auditing your tables.

The code snippet below creates triggers on the active schema.  If we query the sys.triggers , sys.tables and sys.schemas system catalog views, we can list the newly created triggers.

As expected, there are three triggers for the three tables in the active schema.  See screen shot below for details.

Testing audit security

It is important that the audit logs and/or triggers created in this process can’t be tampered with by the average user.  Otherwise, why create our auditing design pattern?

Let’s connect to the SQL Server engine using the hippa_user account.  Since there are not login entries for this contained database user in the master database, it is important to specify the default database using the connection properties tab.

Unlike the jminer account that can see the centralized logging table and custom stored procedure, the hippa_user account does not have any rights to the audit schema.

The typical user can see the audit trigger on the tables.  However, they do not have rights to view the definition of the trigger.  This action executed thru SSMS was denied below.

The worst case scenario would be the ability for the user to drop the auditing trigger.  Again, the typical user does not have such rights.  See the denial of this action via SSMS below.

In a nutshell, we now have auditing in place on the three user defined tables in the active schema.  Add new users to the hippa_role to keep everything secure.  The next task in this proof of concept is to test auditing of DML statements.

Testing audit logging

The software development life cycle dictates that we test each object that we create.  Some testing scenarios have both positive and negative inputs. 

For instance, if we bulk insert into an Azure SQL database, does the stored procedure work if the file exists. That is our positive test case.  In contrast, does the bulk insert correctly fail and alert us when the file does not exist?  That is our negative test case.  Let’s start testing.

The Campbell family has visited the urgent care center today.  The hypothetical application software returns all patients with the correct last name.

Ashely is complaining of influenza like symptoms.  A record of her vitals is logged for the visit.

However, the data entry was incorrect since she has a low grade temperature.  An update to the record is made by the nurse.

The current software keeps the most recent visit in the active schema.  Thus, the older record is deleted from the system.

The above actions might be a test script you write for your system.  Did the actions get correctly recorded to the log table?  Of course the answer is yes.  We have an insert, update and delete action stored as positive test cases.  We can consider the select statement a negative test case since nothing is logged for that action.

See the image below for details.

If you double click on the xml document, SQL Server Management Studio will open the information in a new window.  The screen shot below shows the record that was deleted from the visit_info table.

This design pattern is better than SQL Server Auditing.  Not only does it record changes, the information to readily available to reverse the unwanted action.

Reversing Unwanted Actions

As a database administrator, there will be times in which the business line will want to reverse an unwanted action.  This section goes over the code necessary to perform these tasks.

The delete statement can be reversed with an insert statement.  I am not using the chg_id in the where clause since there is only one action.  In real life, you would supply this value.  The code below inserts the captured xml record.

The update statement can be reversed with another update statement.  Again, I am not using the chg_id in the where clause since there is only one action.  The code below updates the current record with the captured xml record.

The insert statement can be reversed with a delete statement.  Please supply the chg_id when you use this code in production.  The code below deletes the current record.

Unfortunately, each of the three statements is tightly bound to the table structure of the audited table.  I leave the task to dynamically create the statement, given the schema and table name, as an exercise for you to try.

Preventing unwanted actions

So far, the article has been centered on auditing unwanted actions.  How can we prevent inserts, updates and/or deletes on the doctor_info table?  Table triggers can be used to ROLLBACK the unwanted actions.

The code below prevents changes from happening to our static table.

Testing the preventative trigger

Right now, we have two triggers defined on one table.  I do not want to log audit actions that will be rolled back.  Therefore, I will use the code below to disable the audit trigger.

This action was blocked by our user defined trigger.

Many people have a nick name of Jack but a real first name of John.  Let’s try changing Mr. Kevorkian’s first name.

Again, this action was blocked by our user defined trigger.

For some reason, many of the older patients under Jack Kevorkian’s care have died.  The director of the urgent care center has decided to let him go.  Let’s try deleting his record from the system.

Okay.  Something unexpected has happened.  There is a foreign key constraint on the visit_info table.  Therefore, we can’t remove Mr. Kevorkian’s record without producing orphan records.

I do not care about bad data since I am testing out my new trigger.  We can disable the foreign check constraint with the following command.

After trying to remove his record a second time, we get the expected result.

To recap this section, table triggers can be used to prevent unwanted user actions.  Any table constraints violated by an action will fire first before our user defined trigger.

Today, we implemented an audit design pattern using an audit schema, a central audit table and audit table triggers to log all user actions against a group of tables.  This design is an improvement over standard SQL Server auditing since we have captured the actual changed data as an XML document.

Any unwanted insert, update or delete actions can be reversed using the XML record data.  Sample code for each DML statement was created and tested.

Unlike SQL Server auditing, table triggers can be used to prevent actions by rolling back the offending transaction.  Again, the three standard DML statements were tested.  It was surprising to find out that table constraints that are violated are triggered before any custom after triggers.

In short, the new design pattern satisfies stricter business requirements for auditing.  However, this design pattern does not capture the records that an employee is looking at via a SELECT statement.  In a future talk, I will go over custom stored procedures to track what data is viewed or manipulated by a user.

• Exploring the available audit action groups.
• Using database triggers to audit or prevent database changes.
• Using custom stored procedures to track actual data that was viewed and/or manipulated by a user.

About the author

Comments For This Article

Related Content

Designing Tables for Audit Data in SQL Server

Auditing SELECT statements in SQL Server 2008

SQL Server 2008 Auditing Feature

SQL Server 2008 TSQL Auditing Commands for SELECT statements

SQL Server 2008 Audit Change Group

Auditing Failed SQL Server Transactions Using Table Variables

Prevent Confidential Data at the Application Layer from getting into SQL Server

Related Categories

Auditing and Compliance

SQL Injection

Surface Area Configuration Manager

Development

Date Functions

System Functions

JOIN Tables

SQL Server Management Studio

Database Administration

Performance

Performance Tuning

Locking and Blocking

Data Analytics \ ETL

Microsoft Fabric

Azure Data Factory

Integration Services

Popular Articles

SQL Date Format Options with SQL CONVERT Function

SQL Date Format examples using SQL FORMAT Function

SQL Server CROSS APPLY and OUTER APPLY

DROP TABLE IF EXISTS Examples for SQL Server

SQL Server Cursor Example

SQL CASE Statement in Where Clause to Filter Based on a Condition or Expression

Rolling up multiple rows into a single row and column for SQL Server data

SQL Convert Date to YYYYMMDD

SQL NOT IN Operator

Resolving could not open a connection to SQL Server errors

Format numbers in SQL Server

SQL Server PIVOT and UNPIVOT Examples

Script to retrieve SQL Server database backup history and no backups

How to install SQL Server 2022 step by step

An Introduction to SQL Triggers

Using MERGE in SQL Server to insert, update and delete at the same time

List SQL Server Login and User Permissions with fn_my_permissions

SQL Server Loop through Table Rows without Cursor

How to monitor backup and restore progress in SQL Server

SQL Server Database Stuck in Restoring State

• Oracle Fusion Applications
• Oracle EBS R12
• Oracle Fusion Middleware
• Core Oracle Technology
• Oracle Banking & Billing Management
• Buy Certification Mock Exams

Fusion Blog

• Fusion Financials Latest Articles
• Oracle Fusion Applications Training Index
• Oracle Fusion HCM
• Fusion Procure To Pay
• Fusion PayRoll
• ADF Training
• APEX-Oracle Application Express
• Weblogic Server
• Oracle Data Integrator
• Oracle Identity and Access Management
• OFSAA - Oracle Financial Services Analytical Applications
• Common Apps Training Articles
• iProcurement
• Oracle Grants
• Financial Modules
• Supply Chain & Manufacturing
• R12 11i Differences
• Financials Documents
• AME and Self Service HRMS
• Financial Functional Documents
• Receivables
• General Ledger
• Oracle HRMS Payroll Migration
• HRMS Miscellaneous
• Oracle Payroll
• Oracle HRMS Payroll Training Index
• Oracle Workflows Training Index
• Other R12 Articles
• OA Framework Training Index
• Oracle Scripts
• General Apps Topics
• Oracle Apps Interview Questions
• Virtual Machines
• BI Publisher - XMLP
• Mobile Supply Chain Application Framework
• Sunil Khatri
• Jayashree Prakash

Ashish Harbhajanka

• Naveen Kumar
• Surinder Singh
• Krishnakumar Shanmugam
• Trending Authors
• Prasad Parmeswaran
• Kalimuthu Vellaichamy
• Kishore Ryali
• Sivakumar Ganesan
• Senthilkumar Shanmugam
• Prasad Bhogle
• Prabhakar Somanathan
• Ranu Srivastava
• Ahmad Bilal
• TheTechnoSpider
• Anshuman Ghosh

Oracle Gold Partners, our very popular training packages ,   training  schedule is listed here Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

Search Courses

Creating custom ess job to get assignment audit history in oracle hcm cloud application.

Introduction

One of the business need for an HCM Cloud implementation project is to get detailed information about the Assignment Changes which has taken place in the application along with the insight of who performed the change and when. These details can be fetched if the Audit Functionality is enabled.

In this article we will try to demonstrate how we can create a custom ESS job which can be used to get Assignment Audit History Table.

Worked-Out Example

We would have to follow the below mentioned high level steps in order to accomplish the same:

Enable Auditing for Assignment Business Object

• Create a custom data model
• Create a custom report
• Create a custom ESS Job
• Run ESS Job to get results.

Navigation: Setup and Maintenance -> Search (Manage Audit Policies)->Configure Business Object Attributes

Creating a Custom Data Model

We would need to create a custom data model using SQL.

Create a Custom Report

We would now need to create a report from the data model. The Report would appear as below:

Create a Custom ESS Job

In this step, we would create a custom ESS job.

Navigation->Setup and Maintenance-> Search ( Manage Enterprise Scheduler Job Definitions and Job Sets for Human Capital Management and Related Applications )

Before we proceed with creating a new Custom ESS job we could create a custom List of Values which we will utilize for “Assignment Action Code”.

Navigation->Setup and Maintenance-> Search ( Manage Enterprise Scheduler Job Definitions and Job Sets for Human Capital Management and Related Applications )->Manage List of Values Sources

Details to be populated in the LOV are as below:

Now we will create the ESS Job. Details used are as below:

Parameter Details:

Verification

As a last step we would try to run the newly created custom ESS job “Assignment Audit Report”

Navigation: Tools-> Scheduled Processes -> Assignment Audit Report

And once we click on “Default Document” an excel file will open which would appear as below

You can download the files from the link below:

AssignmentAuditReport.xdo.catalog

AssignmentAudit_dm.xdm.catalog

AssignmentAudit.sql

Add comment

Name (required)

E-mail (required, but will not display)

Notify me of follow-up comments

About the Author

Oracle Fusion HCM Techno Functional Consultant with overall 10 years of Experience in software industry with 5 years in EBS HRMS and rest 5 in Fusion HCM.

My areas of intesrest in Fusion HCM include :

a) Inbound Outbound Integration using FBL/HDL or BIP/HCM Extracts.

b) Fast Formula

c) BIP Reports

d) OTBI Reports

e) RESTFUL API / Web Service Call

f) Functional Setup

g) End to End Testing

h) Regression Testing

i) Preparing COnfiguration Workbooks

j) Creating Speed Solutions

k) Preparing User Guides

More articles from this author

• Performing Mathematical Calculations on an Extract Rule Attribute
• Control Absence Type Visibility Based on Plan Balance
• A Worked Out Example of Changing Label Name in Unified Sandbox
• Restrict Changes on Approved Historical Absences
• ObfuscatedSwedishWorkerAutomated HCM Extract in Oracle Fusion HCM Cloud
• Developing Low-Code/No-Code Reports in Oracle ERP Cloud Application
• WHAT Is SQL
• Creating Fusion BIP Reports From Static Data
• Creating a Waterfall Graph In Oracle ERP Cloud Application
• Allow Absence Recording by Specific Admin Users
• Verifying Workforce Structures Position Row In Oracle HCM Cloud Application
• Displaying Impacted Assignment Details when Position Definition is changed
• Alternative Absence Flow on Overbooking Absence
• Smart Navigate to Person Management from Report Output Column
• Creating 90 Days Absence Plan
• An Overview of Master Report - Absence Data Integrity Detection
• Control Absence Type Visibility Based on Plan BalanceV3
• Absence Setup to Restrict Absence Recording to 6 hours per month
• An Introduction to HCM Event Diagnostics in Oracle HCM Cloud
• An Overview of User Experience Related Changes Introduced in Release 13 Update 18B

Search Trainings

Fully verifiable testimonials, apps2fusion - event list, enquire for training.

Get Email Updates

Powered by Google FeedBurner

Latest Articles

• OIC Agent Installation Failed !!! Incorrect OIC Username / Password provided
• OM & AR Setups In EBS R12 Part 3
• OM & AR Setups In EBS R12 Part 2
• Fixed Asset Flexfields in Oracle Assets EBS R12
• OM & AR Setups In EBS R12 Part 1

• Join us on Facebook
• Self Paced Courses
• Custom home
• About Apps2Fusion
• Corporate Trainings
• Finance Transformation Experts

Popular Articles

• Subscribe to New Oracle Apps Articles on this Website by Email
• Basic Concepts - List of useful Oracle Apps Articles
• XML Publisher and Data Template - SQL Query to develop BI Publisher Reports
• OA Framework Tutorials Training
• Some Commonly Used Queries in Oracle HCM Cloud

Apps2Fusion are passionate about Fusion Cloud E-Learning and classroom trainings. Training is our core business and we have been doing this for many many years. We work hard to advise trainees with right career paths. We have published various five star rated Oracle Press Books each was best sellers in its category. We have helped many and could help you as well. 

OUR COMPANY

• Tables and Views for HCM

HCM Tables and Views

This guide contains the information about tables within Oracle HCM Cloud and their columns, primary keys, and indexes. The guide also includes the information about views within Oracle HCM Cloud along with the columns and queries associated with each view. For your reference:

Tables are the basic unit of data storage in Oracle HCM Cloud, where data is stored in rows and columns. For example, the PER_ASG_RESPONSIBILITIES table stores general information about a person's areas of responsibility defined by reference to existing workforce structures. This table stores attributes such as start and end date of responsibility, type of responsibility, status, organization, location, and assignment category.

A view is a logical representation of a table or combination of tables. A view is a stored query that derives its data from the tables on which it is based. For example, the PER_DISPLAY_PHONES_V view shows attributes such as phone type, phone number, speed dial number, extension, country code, and person identifier.

Audit details are not captured in worker assignment related audit tables

We want to capture audit details for worker assignment, worker work term, worker work relationship business object level. However these details are not captured even after enabling Manage Audit Application policies for Global HR.

Content (required):

We want to capture audit details for worker assignment, worker work term, worker work relationship business object level. When an employment flow related transaction is done then we want to capture insert/update/delete details.

Details are not captured in audit table .

PER_ALL_ASSIGNMENTS_M_

PER_ASSIGNMENT_SUPERVISORS_F_

PER_ASSIGN_WORK_MEASURES_F_

When an user with IT auditor navigates to Tool>Audit Report . User couldn't see any data.

• Category 52
• Configuration
• Category 52-2

Howdy, Stranger!

To view full details, sign in.

Don't have an account? Click here to get started!

• Manage VIP Account
• Register for VIP Plan
• VIP Member-Only Content
• HCM Data Loader
• HCM Extract
• BI Publisher
• Fast Formula
• OTBI Analytics
• Personalizations
• Scheduled Processes
• Absence Management
• Performance Management
• Talent Management
• Time & Labor
• HCM Extracts Questions
• HCM Data Loader Questions
• BI Reports Questions
• Report Issues/suggestions
• Your Feedback counts
• Write for Us
• Privacy Policy
• Join Telegram Group
• Join LinkedIn Group
• Join Facebook Page

SQL Query to get the modified fields from the Assignment table using Audit functionality

• Post category: BI Publisher
• Post comments: 0 Comments
• Post last modified: April 5, 2021
• Reading time: 28 mins read

Do you know how to get the updated/ created/ deleted fields from Audit tables when auditing is enabled? Read on to know more details.

This is a VIP member-only content.

Please sign-up for the VIP Membership to get access to this content.

If you have already signed up, please Login  to access the content.

You Might Also Like

How to extract the City, County, State, Zip Combination Geo Codes from Vertex?

How to use the Sensitive Data Access Audit feature from 21B?

How to display No Data Found in BI Report when there is no matching data found?

Session expired

Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.