Difference between revisions of "Email Notification Configuration"

From SmartHCM Wiki
Jump to navigation Jump to search
(Created page with "<pre> -- Connect Sys user and execute following GRANT EXECUTE ON UTL_HTTP to SMARTHCM_SADAD; GRANT EXECUTE ON UTL_TCP to SMARTHCM_SADAD; GRANT EXECUTE ON UTL_SMTP to SMARTHCM_...")
 
 
(7 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 +
Connect Sys user with SYSDBA rights and execute following
 +
<pre>
 +
GRANT EXECUTE ON UTL_HTTP to &db_user;
 +
GRANT EXECUTE ON UTL_TCP to &db_user;
 +
GRANT EXECUTE ON UTL_SMTP to &db_user;
 +
GRANT EXECUTE ON UTL_INADDR to &db_user;
 +
</pre>
 +
 +
Connect Sys user with SYSDBA rights and execute following
 
<pre>
 
<pre>
-- Connect Sys user and execute following
 
GRANT EXECUTE ON UTL_HTTP to SMARTHCM_SADAD;
 
GRANT EXECUTE ON UTL_TCP to SMARTHCM_SADAD;
 
GRANT EXECUTE ON UTL_SMTP to SMARTHCM_SADAD;
 
GRANT EXECUTE ON UTL_INADDR to SMARTHCM_SADAD;
 
 
 
begin
 
begin
 
   dbms_network_acl_admin.create_acl (
 
   dbms_network_acl_admin.create_acl (
     acl        => 'SendEmailService.xml',
+
     acl        => 'email_sending.xml',
     description => 'Send Email via Webservice',
+
     description => 'Send Email',
     principal  => 'SMARTHCM_SADAD',
+
     principal  => '&db_user',
 
     is_grant    => TRUE,
 
     is_grant    => TRUE,
     privilege  => 'connect'
+
     privilege  => 'connect',
 +
    start_date  => SYSTIMESTAMP,
 +
    end_date    => NULL
 
     );
 
     );
 
     commit;
 
     commit;
Line 20: Line 25:
 
begin
 
begin
 
   dbms_network_acl_admin.add_privilege (
 
   dbms_network_acl_admin.add_privilege (
   acl      => 'SendEmailService.xml',
+
   acl      => 'email_sending.xml',
   principal => 'SMARTHCM_SADAD',
+
   principal => '&db_user',
 
   is_grant  => TRUE,
 
   is_grant  => TRUE,
   privilege => 'resolve'
+
   privilege => 'connect'
 
   );
 
   );
 
   commit;
 
   commit;
Line 31: Line 36:
 
begin
 
begin
 
   dbms_network_acl_admin.assign_acl(
 
   dbms_network_acl_admin.assign_acl(
   acl  => 'SendEmailService.xml',
+
   acl  => 'email_sending.xml',
   host => 'sadad.smarthcm.com'
+
   host => '*',
 +
  lower_port  => 80,
 +
  upper_port  => 80
 
   );
 
   );
 
   commit;
 
   commit;
 
end;
 
end;
 
/
 
/
 +
</pre>
 +
 +
Set "WEB_SERVICE_INTEGRATION" value in "SmartHCM Application Integration Setup" screen.
  
-- Test it
+
Test it using below;
begin
+
<pre>
  -- Call the procedure
+
BEGIN
  set_send_email_pkg.send_email_via_wc('smtp.gmail.com',
+
SET_SEND_EMAIL_PKG.SEND_EMAIL('yourname@gmail.com',
                                      '587',
+
                                              NULL,
                                      'sadad.smarthcm.com@gmail.com',
+
                                              GET_SYS_DEFAULT('EMAIL_SERVER_IP'),
                                      'usman@sofdigital.com',
+
                                              GET_SYS_DEFAULT('EMAIL_SERVER_PORT'),
                                      'Testing Email Subject',
+
                                              GET_SYS_DEFAULT('EMAIL_FROM'),
                                      'Testing Email Body Message');
+
                                              'Test Subject',
end;
+
                                              'Test Message',
 +
                                              NULL,
 +
                                              NULL,
 +
                                              NULL);  
 +
END;
 +
</pre>
  
  
-- To verify the above script is successfully executed  
+
To verify the above script is successfully executed;
SELECT * FROM dba_network_acl_privileges;
+
<pre>
SELECT * FROM dba_network_acls;  
+
SELECT * FROM DBA_NETWORK_ACLS;
 +
SELECT * FROM DBA_NETWORK_ACL_PRIVILEGES;
 +
SELECT * FROM user_network_acl_privileges;
 
</pre>
 
</pre>
  

Latest revision as of 11:59, 28 August 2018

Connect Sys user with SYSDBA rights and execute following

GRANT EXECUTE ON UTL_HTTP to &db_user;
GRANT EXECUTE ON UTL_TCP to &db_user;
GRANT EXECUTE ON UTL_SMTP to &db_user;
GRANT EXECUTE ON UTL_INADDR to &db_user;

Connect Sys user with SYSDBA rights and execute following

begin
  dbms_network_acl_admin.create_acl (
    acl         => 'email_sending.xml',
    description => 'Send Email',
    principal   => '&db_user',
    is_grant    => TRUE,
    privilege   => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL
    );
    commit;
end;
/

begin
  dbms_network_acl_admin.add_privilege (
  acl       => 'email_sending.xml',
  principal => '&db_user',
  is_grant  => TRUE,
  privilege => 'connect'
  );
  commit;
end;
/

begin
  dbms_network_acl_admin.assign_acl(
  acl  => 'email_sending.xml',
  host => '*',
  lower_port  => 80,
  upper_port  => 80
  );
  commit;
end;
/

Set "WEB_SERVICE_INTEGRATION" value in "SmartHCM Application Integration Setup" screen.

Test it using below;

BEGIN
SET_SEND_EMAIL_PKG.SEND_EMAIL('yourname@gmail.com',
                                              NULL,
                                              GET_SYS_DEFAULT('EMAIL_SERVER_IP'),
                                              GET_SYS_DEFAULT('EMAIL_SERVER_PORT'),
                                              GET_SYS_DEFAULT('EMAIL_FROM'),
                                              'Test Subject',
                                              'Test Message',
                                              NULL,
                                              NULL,
                                              NULL); 
END;


To verify the above script is successfully executed;

SELECT * FROM DBA_NETWORK_ACLS;
SELECT * FROM DBA_NETWORK_ACL_PRIVILEGES;
SELECT * FROM user_network_acl_privileges;


Note:

  • Where “host” is the IP of the Email Server (‘*’ means auto find the specific email server)
  • Where “principal” is the Schema / Database Username where import the database