42Q-MES0176-A Oracle Adapter Installation Guide

42Q Home > Tools > Oracle Adapter Installation Guide

Tools

Oracle-MES

Oracle Adapter Installation Guide

Version MES15.80

Revision B1

 

Introduction

This guide is designed to help Oracle DBA/SysAdmin users set up an Oracle Apadapter allowing them to create a database and establish the top directory structure following the standard Oracle application process.

 

Pre-requisites

XX42Q Custom Top and Custom Application Creation

This action is intended for Oracle DBA/SysAdmn only, once this option is selected, follow the next steps: 

1. Create a custom database user XX42Q and provide the grants (connect, resource).
2. Create XX42Q_TOP custom top directory and all its subfolders per the Standard Oracle application process.

 

Figure 1: Top Directory and Its Subfolders

   

   3. To register the Custom Application, navigate to Application Developer > Application > Register. 

  

Name	XX42Q SANM Customization
Short Name	XX42Q
Base Path	XX42Q_TOP
Description	XX42Q SANM Customization

 

Figure 2: Register Applications

     

  4. Navigate to System Administrator > ORACLE > Register. Register the XX42Q database user and password. 

Database User Name	XX42Q
Password	It will be generated by the DBA Administrator
Privilege	Enabled
Install Group	0
Description	XX42Q SANM Customization

 

Figure 3: Oracle Users Form

       5. Navigate to System Administrator > ORACLE > DataGroup. Add the Custom Application to the Data Group. 

 

Application	XX42Q SANM Customization
Oracle ID	APPS
Description	It can be left blank

 

Figure 4: Adding an Application To a Data Group

 

Grant on AQ Package and ACL Setup

This action is intended for Oracle DBA/SysAdmn, once this option is selected, follow the next steps: 

1. Connect to APPS Database, GRANT ALL on the DBMS_AQADM to Custom Schema.
2. Grant Execute on DBMS_AQADM, DBMS_AQIN, and DBMS_AQJMS for database users APPS, XX42Q andXX42QWEB.
3. Set up ACL with the host and port details for inbound connectivity of web services.

 

Copy Files Into the Server

This action is intended for Oracle DBA/SysAdmn, once this option is selected, follow the next steps: 

1. Unzip the work order objects.zip file and move the scripts into UNIX Server in ASCII format.

Note 1: Remember the Path where files are copied.
Note 2: If the zip file is copied, then extract the files from the ZIP file.

       2. log in to the Unix Server and navigate to where the above files are copied.

       3. Run the Oracle Apps Environment script (.env).

       4. Check the permission in the file and if the file doesn’t have the right permission please provide the necessary permission using chmod command for all files that start with XX. Suggested permissions are 666. 

Example: $ chmod 666 XX*
 

Installation Execution

This action is intended for Oracle DBA/SysAdmn, once this option is selected, run the script (SANM_INSTALL_SCRIPT.sh ) by providing the below parameters and follow the next steps:

1. Apps database user name and password (APPS/XXXX).
2. Custom Schema database user name and password (XX42Q/yyyy)
3. Custom TOP: $XX42Q_TOP

Example: SANM_INSTALL_SCRIPT.sh apps/XXXX XX42Q/yyyy $XX42Q_TOP

Check the Log file for more details. The log file will be created with the script execution date and time.

 

Custom Responsibility, Menus, Forms, and Functions

LDT Files

1. The following custom 42Q Responsibilities, Menus, Forms, and Functions will be provided during the installation process. 
2. All the Responsibilities,  Menus, Forms, and Functions are present in the LDT files delivered along with the installation package.

Here is the list of objects available in the LDT files:

Admin OUR

1. XX42Q_ADMIN_RESP.ldt    -   Admin Responsibility.
2. XX42Q_ADMIN_MENU.ldt -   Admin Menu.

 

Responsibility name:XX42Q_ADMIN_RESP.
Menu name:XX42Q_ADMIN_MENU.
S. NO	Form Name	Prompt Name
1	XX42Q_DEF_JOB	42Q Define Discrete Jobs.
2	XX42Q_WO_OB	42Q Work Order Creation and Change.
3	XX42Q_WIP	42Q WIP Move Transactions to Oracle.
4	XX42Q_MTL	42Q Material Transactions to Oracle.
5	XX42Q_WIP_MRMDF	42Q Material Requirements.
6	XX42Q_WIP_OPMDF	42Q WIP Job Operations.
7	XX42Q_TRANS_DATA	42Q Notification Transaction Data Details
8	XX42Q_API_DEF	42Q API Program Defaults.
9	XX42Q_WO_ATTR_LKUP	42Q Work Order Attribute Lookup.

 

Support OUR

1. XX42Q_SUPPORT_RESP.ldt  - Support Responsibility.
2. XX42Q_SUPPORT_MENU.ldt - Support Menu.

 

Responsibility name: XX42Q_SUPPORT_RESP.
Menu name: XX42Q_SUPPORT_MENU.
S. NO	Form Name	Prompt Name
1	XX42Q_DEF_JOB	42Q Define Discrete Jobs.
2	XX42Q_WO_OB	42Q Work Order Creation and Change Inquiry
3	XX42Q_WIP	42Q WIP Move Transactions to Oracle Inquiry
4	XX42Q_MTL	42Q Material Transactions to Oracle Inquiry
5	XX42Q_WIP_MRMDF	42Q Material Requirements Inquiry
6	XX42Q_WIP_OPMDF	42Q WIP Job Operations Inquiry
7	XX42Q_TRANS_DATA	42Q Notification Transaction Data Details Inquiry
8	XX42Q_API_DEF	42Q API Program Defaults Inquiry
9	XX42Q_WO_ATTR_LKUP	42Q Work Order Attribute Lookup Inquiry

 

 

User OUR

1. XX42Q_USER_RESP.ldt   - User Responsibility
2. XX42Q_USER_MENU.ldt  - User Menu

 

Responsibility name:XX42Q_USER_RESP.
Menu name:XX42Q_USER_MENU.
S. NO	Form Name	Prompt Name
1	XX42Q_DEF_JOB	42Q Define Discrete Jobs.
2	XX42Q_WO_OB	42Q Work Order Creation and Change Inquiry
3	XX42Q_WIP	42Q WIP Move Transactions to Oracle Inquiry
4	XX42Q_MTL	42Q Material Transactions to Oracle Inquiry
5	XX42Q_WIP_MRMDF	42Q Material Requirements.
6	XX42Q_WIP_OPMDF	42Q WIP Job Operations.
7	XX42Q_TRANS_DATA	42Q Notification Transaction Data Details Inquiry

All the 42Q OURs specified in the LDT files should be displayed on the Home Page of Oracle. Each OUR contains a list of forms.

1. Navigate to the URL: Login (sanmina.com)
2. Login with the Username and Password.

 

Figure 5: Login Page

 

Figure 6: Home Page

 

Menus and Forms

1. Each responsibility has a menu assigned to it. All the menus that are associated with the 42Q OUR should exist. Forms are used to view, edit the data, and perform the operations.
2. To access, navigate to System Administration > Application > Menu.
3. Query the Menu using one of the menu names XX42Q_ADMIN_MENU.
4. It shows the list of forms that are connected to the menu.

 

Figure 7: XX42Q_ADMIN_MENU

  5. Query the Menu using the menu name XX42Q_SUPPORT_MENU.

 

Figure 8: XX42Q_SUPPORT_MENU

   6. Query the Menu using the menu name XX42Q_USER_MENU.

 

Figure 9: XX42Q_USER_MENU

 

Form Functions

1. Functions are used to define the role of each form.
2. To access navigate to System Administration > Vision operations(USA) > Application > Function.

Figure 10: Form Functions Homepage

   

       3. Query the Function using the 42Q function name  %XX42Q%.
       4. The list of form functions with their function and user function name is displayed.

Figure 11: Function and User Function Name

 

Add XX42Q_MES_INTERFACE as an Inventory, Item category, Flexfield

This action is intended for Oracle DBA/SysAdmn, once this option is selected, follow the next steps: 

1. Navigate to Inventory Responsibility > Setup > Flexfields > Key > Segments.
2. Query for Application as Inventory and Flexfield Title as Item Categories and Enter below details: 

Code	XX42Q_MES_INTERFACE
Title	XX42Q_MES_INTERFACE
Description	42Q Interface
View Name	It can be left blank.
Segment Separator	Period(.)
Enabled	Check

Figure 12: Key Flexfield Segments

        3. Click on the Segments button, and enter the below details:

Number	Name	Window Prompt	Column	Value Set	Displayed	Enabled
10	System	System	SEGMENT1	XX42Q_INTF_SYSTEM_CODE	Checked	Checked
20	Type	Type	SEGMENT2	XX42Q_INTF_SYSTEM_TYPE	Checked	Checked

   

     Figure 13: Segments Summary

    

 

Organization Access

1. Each OUR should be assigned to a specific ORG.
2. Navigate to Inventory Responsibility > Setup > Organization > Organization Access.
3. Enter the required details and save it.

Org	Organization Name
Application	XX42Q SANM Customization
Name	OUR name
Comments	It can be left as blank

 

   Figure 14: Organization Access

  

      4. Each OUR has a list of forms. 
      5. Click the expand button in the 42Q Admin(OUR) to list and open the form.

 

  Figure 15: Oracle Home Page

  

      6. A list of the ORG will be displayed based on the organization access provided for the OUR.
      7. Initially, users should select one of the ORGs to open any form.

 

    Figure 16: Organization List

   

 

 XX42Q WebServices Installation

 42Q Oracle Adapter Typical System Requirements

1. 2 x vCPU
2. 4GB Mem
3. 100GB SSD/HDD

 42Q Oracle Adapter Software Requirements

1. Any OS capable of running java/tomcat (The following guide assumes Unix/Linux type environments)
2. Java 1.8
3. Apache Tomcat 8.5.XX (Exposed to the internet either behind a Reverse Proxy or directly with SSL/TLS enabled although not recommended)

 42Q Oracle Adapter Qualified in

1. OpenJDK 64-Bit 1.8.0_41
2. Apache Tomcat 8.5.66
3. RHEL 6.6 (Sanmina R12 Oracle Infrastructure)
4. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

 Install Java 1.8 and Tomcat 8.5

1. Tomcat can be installed as a service or manually started and stopped.
2. The following helper scripts can be used to start and stop the Web Services.
3. To override the default Log4j2 configuration within the 42Q Oracle Adapter WAR file add the location of the new custom log4j2.xml file to the JAVA_OPTS.
4. Other additional JAVA_OPTS can be added to the start_tomcat.sh such as -Xms or -Xmx, minimum and maximum memory allocation, respectively.

https://docs.oracle.com/javase/8/docs/technotes/tools/unix/java.htmlstart_tomcat.sh
            #!/bin/bash
            JAVA_HOME=<JAVA_HOME>
            export JAVA_HOME
            TOMCAT_HOME=<TOMCAT_HOME>
            export TOMCAT_HOME
            PATH=$JAVA_HOME/bin:$TOMCAT_HOME/bin:$PATH
            export PATH
            # export JAVA_OPTS="$JAVA_OPTS -Dlog4j.configurationFile=/<Path>/log4j2.xml" #Uncomment to specify custom log4j2.xml properties file
            $TOMCAT_HOME/bin/startup.sh
        stop_tomcat.sh
            #!/bin/bash
            JAVA_HOME=<JAVA_HOME>
            export JAVA_HOME
            TOMCAT_HOME=<TOMCAT_HOME>
            export TOMCAT_HOME
            PATH=$JAVA_HOME/bin:$TOMCAT_HOME/bin:$PATH
            export PATH
            $TOMCAT_HOME/bin/shutdown.sh

 42Q Oracle Adapter Web Services Installation

1. Copy the provided files into the Tomcat locations:

<TOMCAT_HOME>/lib
        ucp.jar
        ojdbc6.jar

  <TOMCAT_HOME>/webapps
        Xx42q.war

 42Q Oracle Adapter Web Services Configuration

1. Add the following environment and resource variables to the end of the Tomcat context file:

<TOMCAT_HOME>/conf/context.xml
        <Environment
            description="URL for 42Q ERP Interface"
            name="xx42q.URL"
            value="https://erpuatwaf.42-q.com/xxxxxxxxxxxx/services/hcommsfService"
            type="java.lang.String" override="false" />

        <Environment
            description="Username for 42Q ERP Interface"
            name="xx42q.username"
            value="xxxxxxxxxxxx"
            type="java.lang.String" override="false" />

        <Environment
            description="Password for 42Q ERP Interface"
            name="xx42q.password"
            value="xxxxxxxxxxxx"
            type="java.lang.String" override="false" />

        <Environment
            description="Flag to disable the checking of a valid SSL/TLS Cert to be used for testing purposes only"
            name="xx42q.ignoreSSL"
            value="false"
            type="java.lang.Boolean" override="false" />

        <Environment
            description="Datapool to be used by xx42q webservices"
            name="xx42q.dbpool"
            value="jdbc/xx42q"
            type="java.lang.String" override="false" />

        <Environment
            description="HTTP Timeout in milliseconds to be used xx42q by webservices"
            name="xx42q.httpTimeout"
            value="20000"
            type="java.lang.Integer" override="false" />

        <Environment
            description="HTTP Response Timeout in milliseconds to be used by xx42q webservices"
            name="xx42q.responseTimeout"
            value="20000"
            type="java.lang.Integer" override="false" />

        <Environment
            description="Retry delay on HTTP Error in milliseconds to be used by xx42q webservices"
            name="xx42q.retryDelay"
            value="1000"
            type="java.lang.Integer" override="false" />

        <Resource
            description="R12 Test Data Source for 42Q"
            name="jdbc/xx42q"
            connectionPoolName="xx42q"
            user="XX42Q"
            password="xxxxxxxxxxxx"
            url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxxxxxxxx)(PORT=xxxxxxxxxxxx))(CONNECT_DATA=(SERVICE_NAME=xxxxxxxxxxxx)))"
            minPoolSize="25"
            maxPoolSize="50"
            inactiveConnectionTimeout="20"
            auth="Container"
            factory="oracle.ucp.jdbc.PoolDataSourceImpl"
            type="oracle.ucp.jdbc.PoolDataSource"
            connectionFactoryClassName="oracle.jdbc.pool.OracleDataSource"
            validateConnectionOnBorrow="true"
            sqlForValidateConnection="select 1 from DUAL"/>

 2. To override the warnings in the log files add the following lines at the end of the Catalina properties file: 

<TOMCAT_HOME>/conf/catalina.properties
        javax.xml.parsers.DocumentBuilderFactory=com.sun.org.apache.xerces.internal.jaxp.DocumentBuilderFactoryImpl
        javax.xml.transform.TransformerFactory=com.sun.org.apache.xalan.internal.xsltc.trax.TransformerFactoryImpl
        javax.xml.parsers.SAXParserFactory=com.sun.org.apache.xerces.internal.jaxp.SAXParserFactoryImpl
        javax.xml.datatype.DatatypeFactory=com.sun.org.apache.xerces.internal.jaxp.datatype.DatatypeFactoryImpl