Invoking On-Demand Alert from FND_REQUEST.SUBMIT_REQUEST

We can invoke the On-Demand Alerts from PL/SQL programs using the Concurrent Program submission API. Steps provided below:

Consider the below Periodic alert example Indexes
near maximum extents

Click on the Alert Details and find the input parameters as shown below.

Sample Pl/Sql
Procedure to call Periodic alert from FND_REQUEST.SUBMIT_REQUEST.
Create or replace PROCEDURE XX_email_notif(p_Index_id IN NUMBER, P_ret_msg OUT VARCHAR2)
        v_alert_id   NUMBER (10);
        v_appl_id    NUMBER (10);
        v_appl_nm    VARCHAR2(240); 
        x_conc_id    NUMBER (15);
        l_count      NUMBER := 0;
        Cursor c_get_alert_dtl IS
           SELECT alert_name,alert_id, application_id
             FROM alr_alerts
            WHERE alert_name=’Indexes near maximum events’;
      IF p_index_id is NULL
         P_ret_msg := ‘Error :: Unable to Retreive Index Details..’;
  L_available_extents := 5;
  L_index_name:= ‘INDEX_F1’;
  L_user := fnd_profile.value(USERID);
        — GET the ALERT DETAILS
        OPEN  c_get_alert_dtl;
        FETCH  c_get_alert_dtl INTO  v_appl_nm, v_alert_id, v_appl_id;
        CLOSE  c_get_alert_dtl;
        IF v_alert_id > 0
                 ‘ALECDC’, –Periodic alert
                 ‘Detects indexes that are within the special segments’, — alert description(Screen Shot 1)
                 to_char(sysdate), –Date of Submission of the Program
                          v_appl_id, —Application Id registered alert
                          v_alert_id, —Alert Id
                          ‘A’, –Action Set to be performed                                                                
        ‘Messages to DBA’,–SpecifyAction set name defined in alert (Screen Shot 3)
                          ‘Available_EXTENT=’||l_available_extents,(Screen Shot2)
        ‘INDEX_NAME =’||l_index_name, (Screen Shot 3)
        ‘USER =’||l_user, (Screen Shot 3)
        CHR(0) ) ;
               DBMS_OUTPUT.put_line (x_conc_id);
               IF   x_conc_id > 0
           P_ret_msg := ‘Alert triggered to notify team members about this Index (Alert# : ‘||x_conc_id||’)’;
          P_ret_msg := ‘Error :: Unable to trigger Alert.’;
               END IF;                 
              P_ret_msg := ‘Error :: Alert Not Set-up in the System..’; 
          END IF; 
          P_ret_msg := ‘Alert Notification NOT triggered..’;
      END IF;
   END IF;
 END XX_email_notif;   
Note:- The input
parameter for alert need to be passed along with input parameter name defined
in alert.
Ex., ‘Available_EXTENTS=||l_available_extents , ‘INDEX_NAME=||l_index_name and ‘USER =||l_user,  are  Input Parameter name
defined in the alert as shown in the above screen shot.



  • October 11, 2015 | 34 views