Alert

[Saad Qaiser Khan]

Hi,

I have an alert for gme_batch_steps table. The query is showing only one row. But the alert notification i am getting is twice. Because two columns are getting update actual start date and statp status. How can i cater this situation because i want to get the notifcation only once. I have checked suppress duplicates.

Regards,
Saad

[ErmanArslansOracleBlog]

Go through the following document:

How To Determine Why Oracle Alerts Sends Multiple Emails (Doc ID 1450319.1)

Also send me the alert query, the trigger and the alert definition as a whole.

[Saad Qaiser Khan]

select gbh.batch_no,
msi.segment1 || '-' || msi.segment2 || '-' || msi.segment3,
msi.description,
mr.lot_number,
mr.subinventory_code,
mr.RESERVATION_QUANTITY
into
&p_doc_no,
&p_item_desc,
&p_desc,
&p_lot_no,
&p_sub_inv,
&p_resrv_qty
from
gme_batch_header_vw gbh,gme_material_details gmd,mtl_reservations mr,mtl_system_items msi
where gbh.batch_id=gmd.batch_id
--and gbh.batch_no='AR02072'
and gmd.line_type=-1
and gmd.material_detail_id=mr.demand_source_line_id
and msi.inventory_item_id=gmd.inventory_item_id
and msi.organization_id=gmd.organization_id
and mr.lot_number is not null
and :rowid=mr.rowid



The table is mtl_reservations the trigger is update...

The thing is that on update it is sending correct notification but on insert it is just throwing multiple notification however it should not send becasue i did not set alert on insert.

What is happening in the query is whenever the batch is autodetailed the lots are populated in mtl reservation table. But when user manually change the subinventory, lot or quantity reserved it should send alert...

The other alert we need is on insert when mutiple entries are done in resrevation means two lots or three lots than alert is fired. this is the second alert we need.

My alert is also firing on insert even when i did not set my trigger to insert.

I am also looking at the document you have provided.

Thanks and Regards,
Saad

[ErmanArslansOracleBlog]

Did you check the event trigger created on the relevant tables? Did you check its creation script? Is it defined as an "after insert or update"?

[Saad]

Yes The table is mtl_txn_request_headers which have different header statuses . When the move order is created and saved its created with status of incomplete. Now i set my header status with approved and set my trigger to update.

Facing these types of problems a lot in alert. Still vague that why it happened.

Thanks and Regards,
Saad Qaiser Khan

[ErmanArslansOracleBlog]

What type of trigger was created on that table? (when you define an event alert on it)
Was it "update trigger" or "update + insert" trigger?

[Saad Qaiser Khan]

One is of Insert and one is of Update. Because I have two alerts on same table ...

[ErmanArslansOracleBlog]

What is your exact problem?

Is it -> My alert is also firing on insert even when i did not set my trigger to insert?
Or is it ->  the alert notification i am getting is twice. Because two columns are getting update actual start date and statp status.

[Saad Qaiser Khan]

My problem is I am getting two notifications when one move is updated status changed to approved.


Regards,
Saad

[ErmanArslansOracleBlog]

Send me the scripts of those update-triggers..