When the Unimarket-Banner interface encounters an error during document transmission (POs, Invoices, etc.), the Banner APIs generate error logs. These errors are stored in the database and should trigger an automated email notification. To addresses defined in the FTVSDAT records (FZKEPRC-UM ERROR EMAIL-TO).

If these emails are not being received, follow the troubleshooting steps below. To verify the Oracle UTL_MAIL configuration and Access Control List (ACL) permissions.

1. Interface Logging Overview

Before troubleshooting the email delivery, verify that the errors are being captured in the interface tables:

• FZBEPRC: Request log table containing the raw cXML messages from Unimarket.
• FZRCXML: Banner PO and Invoice API log table where specific API return codes are stored.

2. Testing Email Functionality

To determine if the issue lies with the Interface logic or the underlying Oracle database configuration. Run a manual test as the FISHRS user.

SQL Test Script:

begin
  UTL_MAIL.SEND(
    sender     => 'somebody@school.edu',
    recipients => 'somebody@school.edu',
    cc         => 'somebody@school.edu',
    subject    => 'Interface Email Test',
    message    => 'This is a test of the UTL_MAIL function for the Unimarket Interface.'
  );
end;
/

3. Configuration & Permissions Setup

If the test above fails, ensure the following database components are correctly configured.

A. Enable UTL_MAIL Package

Ensure the package is installed and granted to the appropriate schema.

-- Run as SYS or SYSTEM
start /u01/app/oracle/product/[VERSION]/rdbms/admin/utlmail.sql
start /u01/app/oracle/product/[VERSION]/rdbms/admin/prvtmail.plb

-- Grant execution to the Banner Installation schema
grant EXECUTE ON utl_mail TO BANINST1;

-- Set the SMTP Output Server (Replace placeholders with your local SMTP info)
ALTER SYSTEM SET smtp_out_server = '<SERVER_NAME>.<DOMAIN>' SCOPE=BOTH;

B. Configure Network ACLs (Oracle 11g and higher)

Oracle requires explicit permission to communicate with external network services (SMTP).

Step 1: Create the ACL

begin
  dbms_network_acl_admin.create_acl (
    acl         => 'utl_mail.xml',
    description => 'Allow Unimarket Interface mail to be sent',
    principal   => 'BANINST1',
    is_grant    => TRUE,
    privilege   => 'connect'
  );
  commit;
end;
/

Step 2: Assign Privileges to Users

Ensure both the Web User and the Interface User (FISHRS) have access.

-- Grant to WWW_USER
begin
  dbms_network_acl_admin.add_privilege (
    acl       => 'utl_mail.xml',
    principal => 'WWW_USER',
    is_grant  => TRUE,
    privilege => 'connect'
  );
  commit;
end;
/

-- Grant to FISHRS
begin
  dbms_network_acl_admin.add_privilege (
    acl       => 'utl_mail.xml',
    principal => 'FISHRS',
    is_grant  => TRUE,
    privilege => 'connect'
  );
  commit;
end;
/

Step 3: Assign the ACL to the Mail Host

begin
  dbms_network_acl_admin.assign_acl(
    acl  => 'utl_mail.xml',
    host => '<SERVER_NAME>.<DOMAIN>', -- Must match smtp_out_server
    lower_port => 25,
    upper_port => 25
  );
  commit;
end;
/

Common Failure Points

• SMTP Configuration: The smtp_out_server parameter must be reachable from the database server and allow relaying from the DB IP address.
• ACL Missing: If the database was recently upgraded or cloned, ACLs (stored in XML files) may not have migrated correctly.
• Schema Permissions: Ensure the FISHRS user (or whichever user executes the interface jobs) has explicit EXECUTE grants on the UTL_MAIL package.
• Port Blocking: Ensure firewall rules allow traffic on port 25 (or your designated SMTP port) between the Oracle DB server and the Mail server.

Note: For advanced issues where the test script succeeds but interface emails still fail. Verify the FTVSDAT table to ensure the ERROR EMAIL-TO addresses, are correctly formatted and active.