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)
    IS
        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';
    BEGIN
      IF p_index_id is NULL
      THEN
         P_ret_msg := 'Error :: Unable to Retreive Index Details..';
      ELSE
  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
        THEN
   REQUEST_ID := FND_REQUEST.SUBMIT_REQUEST(
                 'ALR',
                 '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);
               COMMIT;
               IF   x_conc_id > 0
               THEN
           P_ret_msg := 'Alert triggered to notify team members about this Index (Alert# : '||x_conc_id||')';
               ELSE                  
          P_ret_msg := 'Error :: Unable to trigger Alert.';
               END IF;                 

          ELSE
              P_ret_msg := 'Error :: Alert Not Set-up in the System..'; 
          END IF; 
      ELSE
          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.