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.
Apps2Fusion Articles 
Comments
Excellent peice of Information!!!
What navigation is used for the Trigger Generation Form ?
Thanks,
-Deb ojyoty
You can do ListOfValues in the responsibility and then search on Function named "%Trigger%"
Al ternately, see the navigation path as below
Thanks,
Anil Passi
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.
bye
Khur shid
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.debu g_enabled;
if g_debug then
hr_utility.set_ location('Enter ing:'|| g_package||'.'| |c_proc, 1);
hr_utility.set_ location('p_ite m_type:'||p_ite m_type,2);
hr_utility.set_ location('p_ite m_key:'||p_item _key,3);
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
hr_utili ty internally makes call to FND Debug Log API.
Hence you can enable the FND Debugging and then look for these messages in fnd_log_message s
Thanks,
Anil
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.
-Chitra
(sele ct MULTI_ORG_FLAG from FND_PRODUCT_GRO UPS will let you know about multi-org setup)
With the idea that dynamic triggers handled date-track transactions more accurately, i decided to use them for a so called "mutation report", that informs the payroll administrator of all the changes that have occurred during the month that may affect the employee's pay. Some remarks why i did not use audit trail and did not use userhooks. One option I never investigated is the use of business events.
Why not audit trail?
Using audit trail for this is an option, but be aware that you'll spend quite some time writing views and functions in order to tweak the 'audit' details into a nice and readable ' changes per employee' end user report. Audit trail stops by telling you: this id changed into this id. Another downside of the audit trail approach is that you always have to run a concurrent program that interprets the date-track transactions, before you can run your end-user report.
Why not a userhook?
A userhook has all the advantages of triggers and handles those datetrack-trans actions in a transparent way. Why then not use userhooks instead of dynamic triggers? The date entered through the standard forms into the tables per_all_people_ f and per_all_assignm ents_f do not call an public API, hence do not provide the benefit of a userhook. 95% of the changes you want to trace are based on those two tables.
Are dynamic triggers the ultimate solution?
No, if you define insert, update and delete (dynamic) triggers all together on a date-tracked table, the trigger will fire more than you want, basically meaning the they do not handle date-track as well. F.ex. for the datetrack mode "Update", the insert trigger will also fire, for the datetrack mode "Update Next Change Only", the delete trigger will also fire..... but with a good understanding of the different datetrack transactions and some sound PL/SQL skills it is fairly easy to get around, compared to trying to stuff everything in database views.
Cheers,
Bert
I need you help, Regarding SIT Validation for error message,
Setp1. user enter the datails in Travel Request SIT.
setp2.then click on submit button,then before submit they need validation that is that details are currect or not.
if it is not currect they need error message display in that SIT Fiorm.
I tryed this manyways
1. using Workflow i validate but error message is not display in that SIT Form.
2.using UserHooks : this hooks will call after Submint Button.
3. Form Persnization: Form persnization is not possible in SIT form.
4. Form Customization also is not possible.
Requ est You to plase Do help.
Thanks
A nji.
I have created a Dynamic Trigger on Per_Periods_of_ service thro other definitions>Dyn amic Triggers. I need to convince the client of using Dynamic Trigger over Database Trigger on the same table.
Can you please gmme some good valid reasons of going ahead with this approach? One of the reason that i know of is that Dynamic Triggers are supported by Oracle while Database triggers are not. This reason however is not that convincing enough to the client.
Please help me on this.It is bit urgent.
Regard s,
Gayatri
RSS feed for comments to this post