This content has been marked as final. Show 14 replies
First of all let me congratulate you for posing the the question so clearly.
Now, coming to the solution, i have used user_hook approach and it works fine . Can you please letme know the shortcomings.
User hook is getting called at the time when employee applies for the leave. The same logic is getting called when the leave is going to be committed.
Please have a look at the function below which returns true if the new leave matches any of the criteria mentioned above and then throws error so that employees are not allowed to go ahead with the erroneous leave.
So when the leave is going to get comitted, it calls below function again via user hook and then treats itself(the existing leave) as pending leave and hence throws the error.
CREATE OR REPLACE FUNCTION APPS.IS_LEAVE_ALREADY_APPLIED_func(p_assignment_id VARCHAR2,
RETURN VARCHAR2 IS
l_decision VARCHAR2(10):= 'FALSE';
CURSOR extisting_unapproved_entry(l_assignment_id VARCHAR2, l_Absence_Type_Id VARCHAR2) IS
hat.status IN ('W', 'Y')
AND hat.assignment_id = psth.assignment_id
AND hat.assignment_id = l_assignment_id
AND hat.transaction_id = pssh.transaction_history_id
AND pssh.transaction_history_id = psth.transaction_history_id
-- AND pssh.INFORMATION5 = l_Absence_Type_Id
AND pssh.INFORMATION5 = paat.absence_attendance_type_id
AND to_date(pssh.INFORMATION1, 'YYYY-MM-DD')> '1-Jan-2012'
AND not exists (select ABSENCE_ATTENDANCE_ID from per_absence_attendances
where ABSENCE_ATTENDANCE_ID = pssh.pk1)
AND exists (select paat.absence_attendance_type_id from Per_Absence_Attendance_Types paat
where absence_attendance_type_id = l_Absence_Type_Id
OPEN extisting_unapproved_entry(p_assignment_id, p_Absence_Type_Id);
FETCH extisting_unapproved_entry INTO record_unapproved_entry;
IF extisting_unapproved_entry%ROWCOUNT = 0
IF (((p_l_s_Date between to_date(record_unapproved_entry.information1, 'YYYY-MM-DD') AND to_date(record_unapproved_entry.information2, 'YYYY-MM-DD')) OR
(p_l_e_Date between to_date(record_unapproved_entry.information1, 'YYYY-MM-DD') AND to_date(record_unapproved_entry.information2, 'YYYY-MM-DD')) OR
( p_l_s_Date <= to_date(record_unapproved_entry.information1, 'YYYY-MM-DD') AND p_l_e_Date >= to_date(record_unapproved_entry.information2, 'YYYY-MM-DD'))) OR
(record_unapproved_entry.INFORMATION5 = p_Absence_Type_Id))
EXIT WHEN extisting_unapproved_entry%NOTFOUND;
you might need to change the formula which calculated the leave duration (which usually is BG_ABSENCE_DURATION).
There you might return an error for entries not compliant with your business.
i was just wondering how would I capture an application error based on 'Calculate Duration' Button.
Can you elaborate?
add the following code to the formula:
duration = 'FAILED'
invalid_msg = 'XX_YOUR_MESSAGE'
return duration, invalid_msg
Let me change the logic and test whether it works fine.
Will post the results here.
I changed the logic, but when i am reasigning the leave while the leave is in transition, it again errors out before comitting to the database.
Does the BG_ABSENCE_DURATION formula get all the validations at the time of applying the leaves as well as at the time of comitting to the db?
The formula is called before submission; my understanding was that the validation was needed before submission.
The DB commit validation is achieved through User Hook (or business event).
I will rephrase my doubt.
Requirement is to validate the leaves just at the time of applying not at the time of commiting to the db.
But, what I observed was that the logic written in this fast formula got called while comitting to the db also.
Hence it threw an error.
Is it so, does the validations written in this fast formula will be called just before comitting to the db also?
what do you mean with "while comitting to the db also"? does it mean when using professional form?
Hope you are doing well.
Sorry for the delayed response, since I was off-sick yesterday !!
'While Comitting to the ab also' means that after all the approvals through SSHR, when the transactions is goint to be comitted, again this BG_Absence_Duration formula is getting called.
I put a validation in BG_Absence_Duration formula to stop employees to apply for similar leaves if existing leave is still not approved.
For e.g -> IF A has applied Privilege Leave from 01-Oct-2012 to 02-Oct-2012. Then he shoould not be able to apply for a new Privilege leave unless
the existing leave is approved.
It works fine while applying, but after approvals none of the leaves are going to database throwing the same error which comes when we apply for a new PL.
Probably , the problem is that this (BG_Absence_Duration) formula is getting called while applying for leave as well as after all the approvals.
May somebody help?
maybe you can work trying to prevent the transaction passing the validation upon submission then failing upon approval.
I tried that way, but not able to do with the function posted in previous post.
I dont have information at the end of the transaction to know whether last approver is approving that record.
If I have that information at the end of the transaction, then I can filter such records to go through.
Probably, I will go to the last resort and extend the contoller of the page to put the validation through Framework.