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.

 

 

 

 

 

 

Oracle Periodic Alert Setup

Periodic Alert:

Periodic alert, checks the database for information according to a schedule you define. In a periodic alert specify the following:

• A SQL Select statement that retrieves specific database information.

• The frequency that you want the periodic alert to run the SQL statement.

• Actions that Oracle Alert to perform once it runs the SQL statement. An action can entail sending the retrieved information to someone in an electronic mail message, running a concurrent program, running an operating script, or running a SQL statement script. We include all the actions we want Oracle Alert to perform, in an action set.

Setting Up an Periodic Alert:

1.  Go to ‘Alert Manager’ responsibility and navigate Alert > Define.

 

·         Enter the name of the application that will own the alert

·         Enter a suitable Name of the alert (up to 50 characters), and give it a meaningful description (up to 240 characters)

·         Select a frequency for your periodic alert  (On-Demand in this case) 

          Defining SQL Query : 

·          Enter a SQL Select statement that retrieves all the data your alert needs to perform the actions you plan to define

·         Your periodic alert Select statement must include an INTO clause that contains one output for each column selected by your Select statement

·         Once you finish specifying the details for your alert definition, you need to create the actions for your alert

·         Do not use set operators in your Select statement

·         You can use PL/SQL functions in your Select statement to fetch complex business logic

·         Click on the ‘Verify’ button to check the select statement is correct

       Click on the ‘Run’ button to execute the Select statement.

 

2] You can view all the input and output column details in ‘Alert Details’ Tab. The Alert Details window includes information such as which Application installations you want the alert to run against, what default values you want your inputs variables to use, and what additional characteristics you want your output variables to have.

§  Identify any inputs with a colon before the name, for example, :INPUT_NAME.

Ex., SELECT user_name,
        password_date,
   :THRESHOLD_DAYS-- Input parameter
   INTO &USER,
   &LASTDATE,
   &NUMDAYS
   FROM fnd_user
   WHERE sysdate = NVL(password_date,
   sysdate) + :THRESHOLD_DAYS
   ORDER BY user_name; 

§  And Create Input in Alert details window as shown below.

§  Identify any outputs with an ampersand (&) before the name, for example, &OUTPUT_NAME.

§  In the Installations tabbed region, specify an Oracle ID if you want Oracle Alert to check your alert against that specific Oracle ID. You can select only those Oracle IDs that are associated with the application that owns your alert

 

3] After you define your alert you need to create the actions you want your alert to perform. For that click on the ‘Actions’ tab.

 

 

 

§  Enter a name (up to 80 characters) and description (up to 240 characters) for your alert action.

§  Select a level for your action: Detail, Summary, or No Exception.

1         Detail action: performs once for each individual exception found

2         Summary action: performs once for all exceptions found

3         No exception action: performs when no exceptions are found.

 

4] Click on ‘Action Details’ tab to display the Action Details window.

 

§  Select the Action Type field as ‘Message’ if you want to send emails. Other action types are: Concurrent Program, Operating System Script and SQL Script.

§  Specify the electronic mail IDs of the recipients you want to send your message to in the To field.

§  If you list more than one recipient in any of these recipient fields, separate each recipient by a space, or a comma, or a combination of the two.

§  You can enter as many recipients as you want, up to 240 characters.

§  You can also enter alert outputs or response variables in any of the alert detail fields. Oracle Alert automatically substitutes the associated output value when checking the alert or the associated response variable value when reading the response.

§  Save your changes.

 

5] Click on ‘Action Sets’ tab in the main Alert Window.

 

§  Once you create your alert actions, you must include them in an enabled action set for Oracle Alert to perform during an alert check. An action set can include an unlimited number of actions and any combination of actions.

§  Enter a Sequence number that lets you order the execution of action sets during an alert check.

§  Give any suitable name and description.

§  Check Suppress Duplicates if you want Oracle Alert to suppress the actions in this action set if the exception found is a duplicate that occurred during the last alert check.

6] Click on ‘Action Set Details’ tab.

 

§  Go to ‘Members’ tab.

§  Find and attach the action that is created in earlier step.

§  Save the changes.

7] Since it is an ‘On Demand’ periodic alert, we can run the alert at any time we want. For that go to Request > Check and enter the alert details. Then click on ‘Submit Request’.

§  This will fire one concurrent program which you can view by going through the navigation:  Request > View

§  View the Log and Output files of the concurrent program to find that the alert is fired successfully.

Done…check your mailbox and you should get emails that are sent from Oracle Alerts.