Loftware - Oracle EBS Post clone steps

Oracle EBS Set Up Tasks:

**Loftware Oracle Integration Services must be restarted after clone**

These steps need to run after every weekly clone

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;

GRANT CREATE ANY PROCEDURE TO SPECTRUM_ROLE;

GRANT DROP ANY PROCEDURE TO SPECTRUM_ROLE;

exec dbms_java.grant_permission(ORACLE_SPECTRUMDEV,'SYS:java.lang.RuntimePermission', 'getenv.TNS_ADMIN', '' );

exec dbms_java.grant_permission(ORACLE_SPECTRUMDEV, 'SYS:java.util.PropertyPermission', 'oracle.net.tns_admin', 'write');

alter user ORACLE_SPECTRUM quota 100M on system;

exec dbms_java.grant_permission(ORACLE_SPECTRUMDEV, 'SYS:java.net.SocketPermission', 'munchkin-prod2.loftwarecloud.com:80','connect,resolve');

GRANT RESOURCE, SPECTRUM_ROLE, JAVA_ADMIN, CONNECT TO ORACLE_SPECTRUM;

To check permissions:

select * from dba_java_policy where grantee = ORACLE_SPECTRUMDEV and ENABLED='DISABLED';

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

execute dbms_java.enable_permission(<SEQ_ID>);

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, Run as sys as sysdba in nonprod PDB

Select value from ORACLE_SPECTRUM.spectrum_config where key = 'spectrumUrl';

Output should show this.

http://munchkin-prod2.loftwarecloud.com:80/spectrum-server/blazeDS/amf

if not update to http://munchkin-prod2.loftwarecloud.com:80/spectrum-server/blazeDS/amf by running below query, if not skip this step.

Update ORACLE_SPECTRUM.spectrum_config set value=' http://munchkin-prod2.loftwarecloud.com:80/spectrum-server/blazeDS/amf ' where key = 'spectrumUrl';

Commit;

Below steps need to run in the customer Loftware call, which is scheduled on Monday 12PM PST
Run as sys as sysdba in nonprod PDB
Run the below query and make sure it has the 150+ rows, if not ask Munchkin Team to run the Loftware integration.

select DBMS_JAVA.LONGNAME(OBJECT_NAME) as OBJECT_NAME from DBA_OBJECTS where OBJECT_TYPE like 'JAVA%' AND lower(object_name) LIKE '%loftware%';

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);

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;