Alerts

[Saad Qaiser Khan]

Hello,

How can i write a group query for alerts.  How does that work?

Regards,
Saad

[ErmanArslansOracleBlog]

group query?
What do you mean?

Please explain what you trying to do in details..

[Saad Qaiser Khan]

SELECT msi.segment1 || '-' || msi.segment2 || '-' || msi.segment3 item,
         gms.subinventory subinventory,
            mil.segment1
         || '-'
         || mil.segment2
         || '-'
         || mil.segment3
         || '-'
         || mil.segment4
         || '-'
         || mil.segment5
         || '-'
         || mil.segment6
            locator_id,
         gms.lot_number lot_no,
         qar.character19 total_rec_qty,
         SUM (NVL (gms.attribute7, 0)) total_sample_quantity,
         qar.character19 - SUM (gms.attribute5) - character22 acc_qty,
         SUM (NVL (gms.attribute5, 0)) rej_qty,
         character22 rej_qty_plan
--    INTO &rowid,
--&p_item,
--         &p_subinventory,
--         &p_locator,
--         &p_lot_number,
--         &p_total_rec_qty,
--         &p_total_samp_qty,
--         &p_accepted,
--         &p_rejec_sam_qty,
--         &p_rej_plan_qty
    FROM gmd_sample_spec_disp gssd,
         gmd_samples gms,
         mtl_system_items msi,
         mtl_item_locations mil,
         qa_results qar
   WHERE     gssd.sample_id = gms.sample_id
         AND msi.inventory_item_id = gms.inventory_item_id
         AND gms.organization_id = msi.organization_id
         AND mil.inventory_location_id = gms.locator_id
         AND qar.lot_number = gms.lot_number
         AND qar.plan_id = 6113
         AND gssd.disposition IN ('4A', '5AV', '6RJ')
         and gms.attribute8 in ('Accept','Accept With Variance')
         AND gssd.disposition NOT IN ('3C')
--         AND gssd.ROWID = :ROWID
GROUP BY gms.subinventory,
         gms.inventory_item_id,
         gms.organization_id,
         gms.locator_id,
         gms.lot_number,
         msi.segment1 || '-' || msi.segment2 || '-' || msi.segment3,
            mil.segment1
         || '-'
         || mil.segment2
         || '-'
         || mil.segment3
         || '-'
         || mil.segment4
         || '-'
         || mil.segment5
         || '-'
         || mil.segment6,
         character19,
         character22
  HAVING qar.character19 = SUM (NVL (gms.attribute7, 0)) + character22

[Saad Qaiser Khan]

The above query works totally fine when we check from backend. When we create a sample scenario it did insert a row. But the alert is not fired. I guess something is wrong with rowid. How can we diagnose if alert is fired or not? How can we properly use query with group by clause.

Regards,
Saad Qaiser Khan

[ErmanArslansOracleBlog]

Verify whether or not  the alert trigger is enabled ->

Ref:

Event Alert Does Not Fire Or Check Event Alert ( ALECTC ) Concurrent Request Does Not Start (Doc ID 1261925.1)

*select dba_triggers.table_name, dba_triggers.trigger_name,
       dba_triggers.status, user_objects.status,
       dba_triggers.trigger_body
from   user_objects, dba_triggers
where  dba_triggers.trigger_name = user_objects.object_name and
       user_objects.object_type = 'TRIGGER' and
       user_objects.object_name like 'ALR_%'

=> One can add the first characters of the table you defined the trigger on.
e.g user_objects.object_name like 'ALR_FND_PROFILE%' , but don't use the full table_name as the trigger name doesn't contain the full table name.


*Verify that the concurrent request ; 'Check Event Alert' has been started and completed normal.

SELECT REQUEST_ID
, REQUEST_DATE
, PHASE_CODE
, STATUS_CODE
, ORACLE_ID
, PROGRAM_APPLICATION_ID
, CONCURRENT_PROGRAM_ID
FROM APPLSYS.FND_CONCURRENT_REQUESTS
where Concurrent_program_id = (select Concurrent_program_id from fnd_concurrent_programs_tl where
USER_CONCURRENT_PROGRAM_NAME = 'Check Event Alert') order by request_date desc;


Also take a look at this note:

Steps Needed to Setup For Event Alert (Doc ID 577392.1)

[Saad Qaiser Khan]

I will surely look into these documents. But we have other alerts too and they are triggering normal. I have issue with this alert only. Also when i make this alert on demand periodic the alert fire fine.

How to take the correct table for event alerts. what is the correct way.

Regards,
Saad

[ErmanArslansOracleBlog]

Review the note ->

Steps Needed to Setup For Event Alert (Doc ID 577392.1)