<-----Google Analytics Code Start----> <-----Google Analytics Code Close---->
Skip to content



Default screen resolution  Wide screen resolution  Increase font size  Decrease font size  Default font size  Skip to content Default color Pink color Green color Green color
Home arrow Technical Articles arrow HR and Payroll arrow Oracle HRMS arrow Triggers in Oracle HRMS and Payroll
This website has now moved to http://apps2fusion.com

Triggers in Oracle HRMS and Payroll | Print |  E-mail
Written by Anil Passi   
Wednesday, 21 November 2007
When it comes to Oracle HRMS or Oracle Payroll, creating triggers on tables is a different preposition.
This is also one of the least documented areas of Oracle HRMS.
Hence in this article, I will touch base upon the basics of creating triggers on Oracle HRMS and Oracle Payroll tables.
In the article that follows this, I will give you a step by step audio-video demo of creating triggers in Oracle HRMS.

What is a Database Trigger?
It is a piece of code that gets executed when changes happen to the data in the table.
The main benefit of trigger is that you get both the old value and also the new value of the record being changed.

Is creating triggers a good practice?
Absolutely not - in Oracle Apps.
You should instead prefer below in priority order listed
1. A business event [if available ] –Best Practice
2. An API Hook [if available]
3. Extension using forma personalization or OA Framework extension [ if possible ]
4. Use CUSTOM.pll [ if applicable ]
5. Extend workflow [ if applicable ].

Why is creating triggers not a good practice?
Because the code written in database triggers is a hidden/invisible code.
Yet, sometimes you have no choice but to create database triggers.
Triggers gain further importance when your logic depends upon both the old and the new value.

Why are triggers in HRMS & Payroll different from creation of triggers in other modules/tables?
Simple answer is date-tracking. Please see the related date-tracking article to understand that concept.

What would happen if I were to create a database trigger on Oracle HRMS table?
Assuming you have read the date-tracking article, read below.
If user makes change to Person name from Passian to Passi, in “update-mode”, then following will happen, assuming person id is 1000
Step 1. Existing record will be end-dated. In this case, the old value of the name , i.e. “Passian” will be retained
Step 2 .A new record will be created with value “Passi”
Effectively two records will be modified/created.
If in case, users were to change the record in CORRECTION-MODE, then just one record will be modified.
As you notice, depending upon how user modifies the record, either 1 record will be modified or two records will be modified.
Hence potentially the database trigger will fire twice. Also, the old & the new value will be the same for the record that gets end-dated.
This explains why you should avoid creating triggers on Oracle HRMS and Oracle payroll tables

How do we overcome this limitation?
This becomes possible by using the Database Trigger screen in Oracle HRMS.
Perhaps in a future article, I will provide an audio-visual demo to give you a live demo.
Database triggers in HRMS can be created from the Trigger Generation.
Benefits of creating trigger through the screen are:-
    1. You trigger code will be fired just once, depending upon whether users modifies record in UPDATE or CORRECTION mode.
    2. The old and the new values passed to the trigger will be accurate regardless of UPDATE or CORRECTION mode for data changes.

Below image shows the screen using which HRMS Triggers can be generated.
Comments (6)add
Navigation for Trigger Generation
written by Debojyoty Sadhukhan , November 22, 2007
Hi Anil,
Excellent peice of Information!!!
What navigation is used for the Trigger Generation Form ?

written by Anil Passi , November 22, 2007
Hi Deb

You can do ListOfValues in the responsibility and then search on Function named "%Trigger%"

Alternately, see the navigation path as below

Anil Passi
written by khurshid , November 23, 2007
HI Anil
very nice information.for people like me who are trying to get into the field of hrms,this is an excellent information.do keep providing informations like these.
Pls Help
written by Jason , November 25, 2007
Dear Anil,
I am currently working on the HRSSA(HR Self service) workflow customizations. within the workflow package one can see the following code:
g_debug boolean := hr_utility.debug_enabled;
if g_debug then
hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);

end if;
i want to debug some code so if i set g_debug boolean to 'true' .how to view the output? is there any tables where the debug statements are stored(eg: po_wf_debug table).
Thanks for your help
written by Anil Passi , November 25, 2007
Hi there

hr_utility internally makes call to FND Debug Log API.
Hence you can enable the FND Debugging and then look for these messages in fnd_log_messages

SO to PO (transfering attachments) with no trigges?
written by ChitraG , November 26, 2007


How do I transfer the Attachments from SO to PO Lines and then to PO Receipt without using Triggers? I understand using triggers is not a best practice.

(select MULTI_ORG_FLAG from FND_PRODUCT_GROUPS will let you know about multi-org setup)

You must be logged in to a comment. Please register if you do not have an account yet.