Hello.

I am supporting an implementation here and trying to work out why Oracle Alerts are not always working.

Scenario: Customer wants the HR department to be notified (via e-mail) whenever a leave of absence is entered, maintained or deleted. Now, this was (for some reason) implemented by putting a before trigger on the PER_ABSENCE_ATTENDANCE table that would insert a new record into a custom table (the custom table has got a column for the type of event).

The custom table is being monitored by the Oracle Alert system (???). This detects any inserts made into the custom table and fires off an e-mail (my vague understanding!).

This all sounds dandy to me (not sure why Workflow was not used?). However, there is a problem. An e-mail is not always being fired off to the HR Dept, and I cannot see why. I am slowly trying to figure out if the trigger is not working, or the alert is not firing, or perhaps there could be an issue with the e-mail system? Does anyone have any possible clues / hints?

Many thanks,
Gavin

Hi Gavin (welcome to hrmsaces by the way )

Ah, Oracle Alert - what a joyous product - many a fond memory of grappling with it...

First thing i would do (have done) is to test the email system. Are you running on a Unix/Linux system or NT? For Unix/Linux you can use the sendmail command to test the email gateway is working. (use the unix 'man' command to get details on the usage of sendmail).
If the gateway is not set up correctly then you will get sendmail alerts to say that the email could not be delivered (when you log into the unix box you would get the "you have new mail" prompt upon logon - use the 'mail' command to check the mail, or get a friendly DBA to do this).
If the mail appears to be going out, is it being intercepted by a spam filter? The format of some of these mails may be odd, and have the machine name or environment name as part of the originator address - some filters could interpret this as spam.

Next up, testing the trigger. Assuming you have a test environment? Key some data into the absence screen - are there rows being populated in the custom table by the trigger?
Why is it a before trigger by the way?

From my memory of alert, you can build quite complex monitoring queries, so in theory you could have the alert driven off the absence table directly, rather than going via a custom table. I presume there is a reason why it has been configured this way.

If there are no rows being populated check the content of the trigger - does it perform condition checking (ie does the absence have to be of a specific type/reason/duration in order for the trigger to work)?

I'll stop now before the reply become unwieldy...Enough to go on for now..

Regards

Barry

Hi Barry,

Many thanks for your response, was very helpful.

I cheated and put a trace statement into the Trigger and have found out why it is not working, phew (I am now a small step closer to finding the solution).

The issue is that when the Trigger was initially developed, the developer put a check in on the Terminal_ID from FND_LOGINS. For anything to be written to the Custom table there must either be "Concurrent" held or there be no row in FND_LOGINS for login_id. See below:

DECLARE
...
l_login_id NUMBER;
l_terminal_id VARCHAR(50);
--
CURSOR get_login IS
SELECT fl.terminal_id
FROM fnd_logins fl
WHERE fl.login_id = l_login_id;
--
...
--
BEGIN
--
l_login_id :=1211978; --:NEW.last_update_login;
OPEN get_login;
FETCH get_login INTO
l_terminal_id;
IF get_login%NOTFOUND THEN
l_terminal_id := 'Concurrent';
END IF;
CLOSE get_login;
--
IF NVL(l_terminal_id,' ') = 'Concurrent' THEN
... Do all the useful stuff.
END IF;
END;

Being a funky (I know I should not be any where near this stuff!) I do not know what terminal_id is and why it needs to be set to "Concurrent". Is this a normal thing?

Thank you!
Gavin

If my hair was long enough, I would have pulled it out by now!

Hmmm
At first glance this suggests to me that they only want the trigger to do "useful stuff" when the upate on the trigger table (absences) has been made by a concurrent program, rather than an online application user (hence the check on FND_LOGINS).

However i'm sure that even concurrent programs have an entry in FND_LOGINS, although i await the slings and arrows of any detractors out there with time on their hands (Clive? )

The big thing though is that the trigger appears to have a hard-coded login id in it
This would mean that unless the login ID 1211978 actually existed in the fnd_logins table the cursor would always fail to find a row and hence would default to 'Concurrent' and thus do the useful stuff.

What would help (of course) would be the functional design behind this customisation - find out what they actually wanted to achieve...

That would be me!! Sorry (I hard coded in a l_login_id that existed to see what would come out, the code works when I put in a login_id that has Concurrent in the terminal_id).

Good shout on the Func Design, I will have to see if we can get our hands on it! (This is not a particually well documented system that we have inherited, unfortunatly!).

I don't know if this helps, but last thing last night I had a chat with a friendly DBA. They mentioned that the terminal_id was used at one point to record the users Terminal (PC) identifier. However, they thought that that was a very long time ago and that it has not been used since...

Hello Gav

<Aside>Are you the 'Gav' who until very recently was at 'Fort Apache' in Gosport?</Aside>

I can't really persue this in the way I would have liked, as for reasons beyond my comprehension I can no longer get access to the Oracle eTRMs. Possibly you (or someone conveniently located near you) might be able to shed some light on the purpose of the column by going here:

https://etrm.oracle.com/pls/etrmlatest/etrm_search.search

Choose the appropriate release level e.g.11.5.10 or whatever, then choose the FND schema, then look for the table FND_LOGINS. Sometimes (but sadly not always) the columns have short descriptions against them.

NB Metalink account AND AT LEAST ONE ACTIVE SUPPORT IDENTIFIER is required to be able to get to see this info.

Like the Murphy's, I'm not bitter...

*kicks cat*

Hi Clive,

That very one! Hello and I hope all is well.

Metalink states that this column is: "Terminal identifier from which user starts this session" - unfortunately not a great deal of help.

Cheers,
Gavin

Gavin, you can run but you can't hide!

The DBA's comment about 'hstorical' usage of terminal id strikes a chord or 2 with me, tbh. I seem to remember in Apps R9 and R10 (Character) access would have been via VT-type terminals, each of which would have had some sort of unique identifier. Possibly true also with PCs under the old 10G client-server arrangement.

*Reaches for sling/arrow*

I think it's quite feasible for a concurrent process to have its own entry in FND_LOGINS - essentially the process must first 'log in' in order to do its thing. This is why, for example, we sometimes run into difficulties with processes that need an entry in FND_SESSIONS - the conc process doesn't do that bit of the login, but it obviously isn't part of the submitting session otherwise the session id would be the same!

Bet you wish you hadn't asked now!

*dabs TCP on facial scratches inflicted by enraged cat*

No Cheers Clive, it is going along with my thinking here. I assume (i know ass of you and me!) that any Workflow processes would therefore log in in the same way as a concurrent request. And, therefore I can assume that the original rational for the process was that absences entered via Self-Service would need to be tracked (absences entered via Forms must be done b HR and so why tell them that they have done it, right!)

I am still trying to get the Workflow working, but I think that once I have done that I will be able to confidently go back and tell the customer that they need this to be re-thought! (Another assumption is that professional forms do not use Workflow (?))

Thanks again.
Gav