We can invoke the On-Demand Alerts from PL/SQL programs using the Concurrent Program submission API. Steps provided below:
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;
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.
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;
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;
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.
what a piece of shit code is this... have you tried running it?
ReplyDeleteits working fine.
ReplyDeleteThanks for sharing artile about OCI Punchout
ReplyDeletecXML PunchOut
this is not working. dont use this
ReplyDeletethis is not working. dont use this
ReplyDelete