Loftware - Oracle EBS Issues Resolution steps
Oracle EBS Set Up Tasks:
The user/schema should be set up in a custom or the USERS tablespace. It should not be on the SYSTEM table.
1) The Oracle user account does not require Oracle database administrator (DBA) privileges. The following are the minimum permissions required for the Oracle user in Oracle. Replace ORACLE_SPECTRUM with the name of the Oracle user.
CREATE ROLE SPECTRUM_ROLE;
GRANT CREATE VIEW TO SPECTRUM_ROLE;
GRANT CREATE TABLE TO SPECTRUM_ROLE;
GRANT CREATE SESSION TO SPECTRUM_ROLE;
GRANT CREATE SYNONYM TO SPECTRUM_ROLE;
GRANT CREATE TRIGGER TO SPECTRUM_ROLE;
GRANT CREATE SEQUENCE TO SPECTRUM_ROLE;
GRANT CREATE PROCEDURE TO SPECTRUM_ROLE;
GRANT RESOURCE, SPECTRUM_ROLE, JAVA_ADMIN, CONNECT TO ORACLE_SPECTRUM;
If you are using Oracle Warehouse Management Solution (WMS), the following privileges are required in addition to the preceding list.
GRANT CREATE ANY PROCEDURE TO SPECTRUM_ROLE;
GRANT DROP ANY PROCEDURE TO SPECTRUM_ROLE;
2) The Customer will have to run the following Database commands to grant further access to the Spectrum Database User:
a. dbms_java.grant_permission('ORACLE_SPECTRUM', 'SYS:java.lang.RuntimePermission', 'getenv.TNS_ADMIN', '' );
b. dbms_java.grant_permission('ORACLE_SPECTRUM', 'SYS:java.util.PropertyPermission', 'oracle.net.tns_admin', 'write');
3) The Customer will have to run the following Database command to add Quota space to the Spectrum Database User:
a. alter user ORACLE_SPECTRUM quota 100M on system;
4) The customer will have to run below command for loadjava permission: Update the URL with customer Spectrum URL
a. dbms_java.grant_permission( 'ORACLE_SPECTRUM ', 'SYS:java.net.SocketPermission', 'xxxx.loftwarecloud.com:80', 'connect,resolve' );
5) To check permissions:
a. select * from dba_java_policy where grantee = 'ORACLE_SPECTRUM';
b. if the output of the query where ENABLED column shows DISABLED, need to run this command to enable them, with by replacing <SEQ_ID> with associated SEQ
c. execute dbms_java.enable_permission(<SEQ_ID>);
6) The Customer will have to review the URL used for the Integration stored in their Oracle database to ensure the proper URL format is being used:
a. Select rowid, a.* from spectrum_config a where key = 'spectrumUrl';
b. Original Value: http://xxxxxxxx:8080/spectrum-server/blazeDS/amf
c. New Value: http://xxxxxxx/spectrum-server/blazeDS/amf
Spectrum side:
1. Set up the Oracle integrations; if it is WMS, check the WMS checkbox.
2. Ops to add their TargetGroups to the port 80 rules on the ALB, prior they were only on the 443 (SSL)
3. Override the integration with port 80 and server.
4. Once the upgrade is successful; set Concurrent servers to “All” if Spectrum is multinode
Steps to resolve Munchkin’s printing process
Many Print jobs were run as these items were identified and resolved
1) Loftware requested that the Loftware Operations team increase the logging.
2) Once the logging was updated, an error in the log indicated that the Java was being accessed correctly so the integration wasn’t able to complete the installation successfully when started in Spectrum.
3) Loftware requested Munchkin to confirm the Oracle grants especially GRANT RESOURCE, ORACLE_SPECTRUM, JAVA_ADMIN, CONNECT TO ORACLE_SPECTRUM
a. Loftware requested Munchkin run the following query: select * from dba_java_policy where grantee = 'ORACLE_SPECTRUM'; The missing grant above can make some of them disabled.
4) Loftware requested that Munchkin check Oracle WMS Job Status page and tell us what the JOB_STATUS column indicated.
5) Loftware requested that Munchkin try the Reprint button to see if it was able to successfully send the job.
These requests indicated a number of issues that combined caused the printing issue.
6) Munchkin Oracle DBA confirmed that the GRANT RESOURCE, ORACLE_SPECTRUM, JAVA_ADMIN, CONNECT TO ORACLE_SPECTRUM was missing and applied it to the user.
7) Some of the rows in the dba_java_policy were still disabled. The DBA ran the following command in Oracle to enable them: execute dbms_java.enable_permission(SEQ_num);
8) Loftware recreated the Spectrum integration in order to force the components to be updated as described. The logs now had a successful update message.
[2023/01/26 18:02:45.542][DEBUG][com.loftware.integration.oracle.contexts.impl.OracleInstanceContextImpl](HeartBeatThread) Oracle client upgraded complete for integration service 10000047
** If the message asking if the user wants to upgrade the integration is not thrown when the integration is started, it is best practice to delete the integration in Access Control and recreate it so that the update is fully done.
9) Loftware set the Concurrent Running Servers to ALL. This has to be done after the integration update was successful.
10) Loftware requested another job be sent.
11) Munchkin confirmed that the JOB_STATUS column in Oracle was blank. This means that it is never getting to Spectrum. There were no log entries (or jobs) in Spectrum which made sense given that field.
12) When Munchkin ran Reprint, an error indicating it could not find the printer was thrown.
13) Loftware requested MWA be restarted. The same error kept occurring after the reboot.
14) Munchkin noticed that the Oracle user was set up as Asynchronous and changed it to Synchronous TC/ICP.
15) Loftware requested Oracle DBA to run below stored procedure in Oracle to confirm that Oracle can run a print job on Spectrum.
set serveroutput on format wrapped;
declare
xmldata varchar2(4000);
jobstatus varchar2(3000);
printerstatus varchar2(3000);
statustype number;
linepos number;
linelen number;
begin
xmldata := '<?xml version="1.0" encoding="UTF-8" standalone="no"?> <!DOCTYPE labels SYSTEM "label.dtd">';
xmldata := xmldata || '<labels _FORMAT="psgportdemo01Upgrade" _QUANTITY="1" _PRINTERNAME="UD_Adobe_PDF" _JOBNAME="LWL4997">';
xmldata := xmldata || '<label>';
xmldata := xmldata || '<variable name="ItemNumber">PN01328-2</variable>';
xmldata := xmldata || '<variable name="ItemDesc">000012</variable>';
xmldata := xmldata || '<variable name="Expiration_Date_Tag">0000-00</variable>';
xmldata := xmldata || '<variable name="REF">AR-1922BCM</variable>';
xmldata := xmldata || '<variable name="LBLQTY">00001</variable>';
xmldata := xmldata || '</label></labels>';
APPS.INV_SYNC_PRINT_REQUEST.SYNC_PRINT_REQUEST(xmldata, jobstatus, printerstatus, statustype);
<AFL User Name>
linepos := 0;
dbms_output.put_line('jobstatus:');
while length(jobstatus) > linepos loop
if length(jobstatus) - linepos > 254 then
linelen := 254;
else
linelen := length(jobstatus) - linepos;
end if;
dbms_output.put_line(substr(jobstatus, linepos, linelen));
linepos := linepos + linelen;
end loop;
dbms_output.put_line(' ');
linepos := 0;
dbms_output.put_line('printerstatus:');
while length(printerstatus) > linepos loop
if length(printerstatus) - linepos > 254 then
linelen := 254;
else
linelen := length(printerstatus) - linepos;
end if;
dbms_output.put_line(substr(printerstatus, linepos, linelen));
linepos := linepos + linelen;
end loop;
dbms_output.put_line(' ');
dbms_output.put_line('statustype: '||to_number(statustype));
end;
16) The job was sent to Spectrum as expected (job failed due to a label that doesn’t exist.) This confirmed that the issue didn’t lie with Spectrum.
17) Munchkin added missing printer to Oracle. When the job was run again as transaction or reprint, it printed but not through Spectrum. It ran directly from Oracle EBS. This was confirmed because the JOB_STATUS column was still blank.
18) Munchkin changed their user to run as SYNCHRONOUS Generic.
19) The print job was sent to Spectrum; it failed due to the full path of the label not being sent.
20) Munchkin updated Oracle to send full printer and label path. The job successfully printed a label in Spectrum.
Other things I’ve encountered:
If the below error is received; please do the following:
“cannot integrate with Oracle WMS. Print mode Synchronous - Generic is already in use"
1. Run the below query:
a. SELECT TEXT FROM all_source WHERE type = 'PACKAGE BODY' AND owner = 'APPS' AND name = 'INV_SYNC_PRINT_REQUEST'
2. If you do not get ‘No Rows Returned’, you will need to check the package APPS.INV_SYNC_PRINT_REQUEST and check to see if it has a BODY. There can be no package BODY.
3. Please execute below command: Please Note: You are only dropping the PACKAGE BODY and not the PACKAGE.
a. drop package body APPS.INV_SYNC_PRINT_REQUEST
4. Run the below query:
a. SELECT TEXT FROM all_source WHERE type = 'PACKAGE BODY' AND owner = 'APPS' AND name = 'INV_SYNC_PRINT_REQUEST'
5. Below is what the PACKAGE should look like when you get ‘No Rows Returned’ from the above query.
6. create or replace package INV_SYNC_PRINT_REQUEST as
a. PROCEDURE SYNC_PRINT_REQUEST(
p_xml_content IN LONG,
x_job_status OUT VARCHAR2,
x_printer_status OUT VARCHAR2,
x_status_type OUT NUMBER );
end;
If you need to get logs from the Oracle server; this is a sample directory structure on the Oracle server.
When collecting trace logs there are two areas
We want the trace logs in the trace folder and also in the Spectrum folder
Integration Client log location example, on our internal test EBS system:
