Workflow mailer Settings and Status Script

In this forum Oracle Applications DBA's, System Administrators & Developers can share their knowledge/issues.
Post Reply
admin
Posts: 2062
Joined: Fri Mar 31, 2006 12:59 am
Location: Pakistan
Contact:

Workflow mailer Settings and Status Script

Post by admin »

Hi,
Here is a sample code to handle following in R12.1.3

1. Check Workflow Mailer settings to OUTBOND is wrong will update with provided info.
2. Will check if Mailer is running if not will try to start and send email/sms.
3. Will change workflow WF_ADMIN_ROLE to * which gets change to SYSADMIN after autoconfig.
4. After autoconfig settings will be reset so this script is good to schedule like hourly to check and update if it is not correct.

NOTE: This script is result of R&D so we will not give any guarantee of this if it will 100% work for your environment so always TEST TEST before moving to LIVE.

CREATE OR REPLACE PROCEDURE XX_mailer_workflow_start (errbuf OUT VARCHAR2,
retcode OUT VARCHAR2)
IS
/* This procedure is to check workflow mailer status and if not running then will try to start and will send email. It is scheduled after every
hour
Notification Mailer Start

By Sikandar Hayat Version 1.1
Sunday 27-SEP-2020
*/

p_retcode NUMBER;
p_errbuf VARCHAR2 (100);
m_mailerid fnd_svc_components.component_id%TYPE;
v_component_status fnd_svc_components.component_status%TYPE;
v_PARAMETER_VALUE APPLSYS.FND_SVC_COMP_PARAM_VALS.PARAMETER_VALUE%TYPE;
l_body CLOB;
PO_ERR_MSG VARCHAR2 (1000);

v_password_string VARCHAR2 (2000) := 'ERPstuff999';
v_encrypted_password VARCHAR2 (2000);
v_errmsg VARCHAR (1000);
v_retcode NUMBER;
BEGIN
-- Due to autoconfig need to reset to *
UPDATE wf_resources
SET text = '*'
WHERE name = 'WF_ADMIN_ROLE' AND text <> '*';

COMMIT;


SELECT component_id
INTO m_mailerid
FROM fnd_svc_components
WHERE component_name = 'Workflow Notification Mailer';

SELECT PARAMETER_VALUE
INTO v_PARAMETER_VALUE
FROM APPLSYS.FND_SVC_COMP_PARAM_VALS
WHERE PARAMETER_ID = (SELECT parameter_id
FROM APPLSYS.FND_SVC_COMP_PARAMS_B
WHERE parameter_name = 'OUTBOUND_SERVER');

IF v_PARAMETER_VALUE <> 'X.X.X.X' -- give IP or hostname of mail server
THEN
-- Outbound settings

UPDATE APPLSYS.FND_SVC_COMP_PARAM_VALS
SET PARAMETER_VALUE = 'X.X.X.X'
WHERE PARAMETER_ID = (SELECT PARAMETER_ID
FROM APPLSYS.FND_SVC_COMP_PARAMS_B
WHERE parameter_name = 'OUTBOUND_SERVER');

UPDATE APPLSYS.FND_SVC_COMP_PARAM_VALS
SET PARAMETER_VALUE = 'info@erpstuff.com'
WHERE PARAMETER_ID = (SELECT parameter_id
FROM APPLSYS.FND_SVC_COMP_PARAMS_B
WHERE parameter_name = 'OUTBOUND_USER');

ECX_OBFUSCATE.ecx_data_encrypt (
l_input_string => v_password_string,
l_output_string => v_encrypted_password,
errmsg => v_errmsg,
retcode => v_retcode);

IF (retcode > 0)
THEN
FND_FILE.PUT_LINE (
FND_FILE.OUTPUT,
'Error while encrypting password: '
|| v_retcode
|| ' - '
|| v_errmsg);
ELSE
FND_SVC_COMP_PARAM_VALS_PKG.load_row (
X_COMPONENT_NAME => 'Workflow Notification Mailer',
X_PARAMETER_NAME => 'OUTBOUND_PASSWORD',
X_PARAMETER_VALUE => v_encrypted_password,
X_CUSTOMIZATION_LEVEL => 'L',
X_OBJECT_VERSION_NUMBER => -1,
X_OWNER => 'ORACLE');

FND_SVC_COMP_PARAM_VALS_PKG.load_row (
X_COMPONENT_NAME => 'Workflow Notification Mailer',
X_PARAMETER_NAME => 'INBOUND_PASSWORD',
X_PARAMETER_VALUE => v_encrypted_password,
X_CUSTOMIZATION_LEVEL => 'L',
X_OBJECT_VERSION_NUMBER => -1,
X_OWNER => 'ORACLE');

COMMIT;
FND_FILE.PUT_LINE (
FND_FILE.OUTPUT,
'Workflow mailer setting were not correct may be due to autoconfig now set.');
END IF;

--Inbound Settings

UPDATE APPLSYS.FND_SVC_COMP_PARAM_VALS
SET PARAMETER_VALUE = 'X.X.X.X'
WHERE PARAMETER_ID = (SELECT parameter_id
FROM APPLSYS.FND_SVC_COMP_PARAMS_B
WHERE parameter_name = 'INBOUND_SERVER');

UPDATE APPLSYS.FND_SVC_COMP_PARAM_VALS
SET PARAMETER_VALUE = 'info@erpstuff.com'
WHERE PARAMETER_ID = (SELECT parameter_id
FROM APPLSYS.FND_SVC_COMP_PARAMS_B
WHERE parameter_name = 'INBOUND_USER');


UPDATE APPLSYS.FND_SVC_COMP_PARAM_VALS
SET PARAMETER_VALUE = 'info@erpstuff.com'
WHERE PARAMETER_ID = (SELECT parameter_id
FROM APPLSYS.FND_SVC_COMP_PARAMS_B
WHERE parameter_name = 'REPLYTO');



commit;

END IF;

SELECT component_status
INTO v_component_status
FROM fnd_svc_components
WHERE component_id =
(SELECT component_id
FROM fnd_svc_components
WHERE component_name = 'Workflow Notification Mailer');

FND_FILE.PUT_LINE (
FND_FILE.OUTPUT,
'As per system Workflow Notification mailer status is: '
|| v_component_status);

IF v_component_status <> 'RUNNING'
THEN
fnd_svc_component.start_component (m_mailerid, p_retcode, p_errbuf);
COMMIT;

-- If need to send SMS
-- INSERT INTO sms.outbox (phoneno, MESSAGE)
-- VALUES ('1234567890', 'Workflow mailer was not running attempted to start.');


skr_email_attachments.send_email (
P_DIRECTORY => 'INVOICES_PDF', --replace with your directory object
P_SENDER => 'info@erpstuff.com',
P_RECIPIENT => 'info@erpstuff.com',
P_CC => NULL,
P_BCC => NULL,
P_SUBJECT => 'ALERT - System trying to start Notification Mailer',
P_BODY => 'Dear DBA, Workflow Notification Mailer was down and system tried to start it. System will check after 1 hour and if not running will try again. Better to check if there is no major issue/error on the server.',
P_ATTACHMENT1 => NULL,
P_ATTACHMENT2 => NULL,
P_ATTACHMENT3 => NULL,
P_ATTACHMENT4 => NULL,
P_ERROR => po_err_msg);
END IF;
END;
Malik Sikandar Hayat
admin@erpstuff.com
Post Reply

Who is online

Users browsing this forum: No registered users and 2 guests