Oracle R12 x DFE Integration Installation procedures

(1)

Oracle R12 x DFE Integration

Installation procedures

Version 1.6

October/2012

(2)

1. Revision

Data da

Revisão

Versão

Documento

Executor

Assunto Revisado

03/05/2010 1.0 Alexandre Souza Document creation

03/10/2010 1.1 Alexandre Souza New requisites and document revision

16/08/2011 1.1 Alexandre Souza

New instructions for seting Oracle EBS setup for integrations.

Removal of instructions for creating event

oracle.apps.synchro.reimpressao.reenvio

24/10/2012 1.6 Alexandre Souza

Data source creation example revied. Parameter Transaction Level should be “Local Transaction Only”

(3)

2. Scope

This document aims to list / explain the installation procedures of the integration between Oracle R12 and Synchro NFe Manager.

3. Audience

IT people responsible by the installation of the integration.

4. Pre-Requisites

1. Execution Environment (both environments are approved): a. Oracle BPEL v10.1.3.1

Oracle Application Server Release 3 (10.1.3.1) b. Oracle BPEL v10.1.3.4

Oracle Application Server Release 3 (10.1.3.4) 2. Oracle Applications R12 Patches:

a. 7000001 R12: SPED - BRAZILIAN SPED PHASE – I (see note 753705.1)

b. Localization Brazil (install the most recent localization kit available at the installation time. If needed, open a ticket at Oracle support);

c. Notification architecture and views CLL (note 960846.1): i. Patch 8931153; ii. Patch 9124703; iii. Patch 9278993; iv. Patch 9315891; v. Patch9645530; vi. Patch 9701975; vii. Patch 10096982. 3. EBS Setup:

a. NFe Setup and IBGE codes for Customers/Suppliers (note 753705.1 );

i. Obs. There is not an IBGE setup for Inventory Organization, because of this we recommend a Flexfield creations in the inventory organization setup to inform the IBGE codes for each organization.

b. Criação do Usuário XXISV (note 960846.1);

c. Activate profile CLL_F255: Enable Business Events for ISV to Yes, to enable the

(4)

d. Define lookup CLL_F255_ISV_EVENTS with the following information (insert): Code: SYNCHRO

Meaning: XXISV.XXISV_SYNCHRO_EVENTS Description: Tabela de eventos Synchro Tag: BPEL

(5)

Para mais informações consulte “ISV Integration Solution User Guide” disponível no note 960846.1.

4. DF-e Connector v 1.7 Build 01

5. Database Configuration

In this section, we will describe the procedure to install the database scripts, that will create tables and scripts in the XXISV schema, according to Oracle policy for ISVs.

5.1. Schema XXISV configuration

• Grants: Log-in into APPS schema with a SQL tool (such as SQL*Plus, toad etc.) and run the script “GRANTS.sql, located in the folder “XXISV” of the installation kit. This script will grant select and execute rights to the XXISV schema;

• Table creation: Log-in into the Oracle schema XXISV with a SQL tool (such as SQL*Plus, toad etc.) and run the script “TABELAS.sql”, located in the folder “XXISV” of the installation kit;

If the customer already has the Synchro Fiscal Solution installed the following tables will also exist in the Synchro schema:

o_{EXPBR_SYN_PAR_INTEGRACAO} o_{EXPBR_SYN_PAR_PARAMETROS} o_{EXPBR_SYN_PAR_LISTA_VALORES} o EXPBR_SYN_PAR_VALORES

In such case, drop these tables from the XXISV schema and create synonyms to the Synchro schema, instead. Please remember to grant insert, update and delete rights for these tables from the Synchro schema to the XXISV schema.

• Synonym creation: Log-in into the Oracle schema XXISV with a SQL tool (such as

SQL*Plus, toad etc.) and run the script “SINONIMOS.sql” , located in the folder “XXISV” of the installation kit;

• Views creation: Log-in into the Oracle schema XXISV with a SQL tool (such as SQL*Plus, toad etc.) and run the script “VIEWS.sql” , located in the folder “XXISV” of the installation kit;

• Functions creation: Log-in into the Oracle schema XXISV with a SQL tool (such as

SQL*Plus, toad etc.) and run the script “FUNCTIONS.sql” , located in the folder “XXISV” of the installation kit;

• Procedures creation: Log-in into the Oracle schema XXISV with a SQL tool (such as SQL*Plus, toad etc.) and run the script “PROCEDURES.sql” , located in the folder “XXISV” of the installation kit;

• Packages creation: Log-in into the Oracle schema XXISV with a SQL tool (such as SQL*Plus, toad etc.) and run the script “PACKAGE.sql” , located in the folder “XXISV” of the installation kit;

(6)

Obs. In this same folder there is a script ”Inclui_sequences_conector.sql”. This script should be run in the Schema where the product “conector padrão” was installed, only if it is Oracle Database. In caso of the data base is no Oracle, two rows should be inserted in the table NFE_SEQUENCE, as follow:

NOME_TABELA VALOR NFE_CONTROLE 1

NFE_XML 1

6. Environment configuration (BPEL and OAS servers)

6.1. BusinessEvent configuration

It is necessary to create new Business Events inside Oracle Application R12. The steps to do that are the following:

The two events to be created are: - oracle.apps.synchro.invoice.complete

- oracle.apps.synchro.reimpressao.reenvio (not used)

The steps to create both events are very similar and must be done according to the following examples:

Atention: It is necessary to check whether the Oracle core business events

oracle.apps.cll.eb.invoice.actionsNfe e oracle.apps.cll.ra_customer_trx are enabled, please check with oracle EBS system administrator.

6.2. Create the events

(7)

(8)

In the next screen, inform the following data and then press the “Apply” buttom: Name oracle.apps.synchro.invoice.complete

Display Name Synchro NFe invoice complete Description Synchro NFe invoice complete Status Enabled

Owner Name FND Owner Tag FND

(9)

In the next screen, inform the following data:

System: Select the desired option (according to the customer) Source Type: Local

Event Filter: oracle.apps.synchro.invoice.complete Phase: 202

Status: Enabled Rule Data: Message

Action Type: Send to Agent On Error: Stop and Rolback After that, press “Next”:

(10)

In the next screen, add the following information: Action Out Agent: WF_BPEL_QAGENT Priority: Normal

Owner Name: Oracle Workflow Owner Tag: FND

Description: Subscription for enqueuing event in WF_BPEL_Q (<<event name>>) After that, press “Apply”.

6.3. Connection configuration

It is necessary to create connections in the Application Server in order to allow BPEL to connect to the database. These connections must be created according to the following data (do not change the values written in red):

Attention, described below are the connections that must be created, use the example (topic below to create the same). Ex

Connection Pool = Example of Creating Connection Pool Example Datasource = creation date Sources

Connection Factory = Example of Creating Connection Factory 1) Connection with XXISV Schema

(11)

Connection Pool: SynchroBpelXXISVDs Datasource: SynchroBpelXXISVDs • Connection Factory to DbAdapter

o_{Name: eis/DB/SynchroBpelXXISVFactory}

o Property xADataSourceName: jdbc/SynchroBpelXXISVDs

2) Connection with APPS Schema

Connection Pool: SynchroBpelAPPSDs Datasource: SynchroBpelAPPSDs

• Connection Factory to DbAdapter and AppsAdapter

o_{Name: eis/DB/SynchroBpelAPPSFactory}

o_{Property xADataSourceName: jdbc/SynchroBpelAPPSDs}

• Connection Factory to AppsAdapter

o_{Name: eis/Apps/SynchroBpelAPPSFactory}

o_{Property xADataSourceName: jdbc/SynchroBpelAPPSDs}

3) Connection with DFe Connector Schema (where the connector tables are installed at) Connection Pool: SynchroBpelNfeDs

Datasource: SynchroBpelNfeDs • Connection Factory to DbAdapter

o Name: eis/DB/SynchroBpelNfeFactory

o Property xADataSourceName: jdbc/SynchroBpelNfeDs

6.3.1. Connection Pool example

p.s. The following images are just an example of how to create connections, the correct values are written in topic 6.3.

(12)

2) At “Cluster Topology”, select the SOA instance, created at the Application Server. This instance is where the BPEL application was installed at and can have any name, according to the DBA preferences at the creation moment. Is our example, its name is “home”:

3) At SOA instance, select “Administration”

(13)

4) At “Administration” select Services > JDBC Resources

(14)

6) At “Create Connection Pool – Application” >” Connection Pool Type” select “New Connection Pool” and press “Continue”;

7) At Create Connection Pool, at tab Home, fulfill the fields as below:

Name: Type the Connection Pool name, for exemple : NFE_CP Connection Factory Class: Use the default class: oracle.jdbc.pool.OracleDataSource JDBC URL: Change the connect string according to the database information:

jdbc:oracle:thin:@//<servername>:port/sid: UserName: Type the schema name: <userid>

Password: Type the schema’s password: <password>

(15)

8) At the Attributes tab, fulfill the data as below:

Minimum Number of Connections: Type the minimum connections that the pool should open: 10 Maximum Number of Connections: Type the maxim number of connection the pool can open: 35 Inactivity Timeout: Type the time, in seconds, after which one inactive connection

can be closed: 60

Wait For Used Connection Timeout: Type the maximum time, in seconds, that the processes will wait for a connection before raising an error: 60

Abandoned Connection Timeout: Type the maximum time, in seconds, that configures an aboandoned connection: 180.

Important: Please notice that these values are suggested. You must fine tune them in order to fit your installation. We strongly recommend that you set the maximum number of connections to a number greater than 30.

(16)

10) To test the database connection, go to JDBC Resources > Connection Pools, find the recently created connection pool (according to the example below), and press “Test Connection”:

11) At “Test Connection”: <<connection name>>” press Test:

(17)

6.3.2. Data Sources creation example

1) At JDBC Resources > Data Sources, press Create:

2) At Create Data Source – Application & Type > Data Source Type select Managed Data Source and press “Continue”:

(18)

3) At “Create Data Source – Managed Data Source” fulfill the following data: Name: Type the Data Source name. For example, NFE_DS JNDI Location: Type the JNDI Name. For example, jdbc/NFE_DS Transaction Level: Use the value: Local Transactions Only

Connection Pool: Use the connection created above, at the step 4.1 (in our example, NFE_CP)

Login Timeout (seconds): Type the maxim time, in seconds, that the processes should wait for a login.

Atention!!! The parameter Transaction Level should be Local Transactions O nly.

4) Press “Finish”.

6.3.3. Connection Factory creation example

1) Select the application DbAdapter of your SOA topology;

(19)

2) Press the option “Connection Factories”;

3) Press “Create”

4) Fulfill the following data:

JNDI Location eis/DB/SynchroBpelNfeFactory xaDataSourceName jdbs/SynchroBpelNfeDs 5) Press Finish.

(20)

6.4. Install the Integration Configuration

1) Select the Application Server topology;

2) Press “Applications”; 3) Press “Deploy”;

4) At the option “Storage”, select the file “SynchroNfeR12ParametrizacaoWeb.war”, available at the integration kit;

(21)

5) Press “Next”;

6) At the option “Application Name”, type “SynchroNFeR12Parametrizacao” and press “Next”;

(22)

8) Uncheck the option “Import” for the libraries “oracle.xml” and “oracle.xml.security”;

9) Press “OK” and “Disponibilizar”;

10) The deploy state should be “Completed”;

(23)

11) At the Application Server, find the application “SynchroNfeR12Parametrizacao”

12) At the “Modules” option, select “SynchroNfeR12Parametrizacao”;

(24)

13) To test the application, press “Test Web Module”:

14) The login screen should be displayed:

6.5. BPEL services deploy

At this step, we will we will make the BPEL services available to the Oracle Applications and update the Oracle APIs.

The processes must be deployed in the following order: • bpel_SynchroNfeR12Emissao_1.0.jar;

• bpel_SynchroNfeR12RetornoEmissao_1.0.jar; • bpel_SynchroNfeR12Main_1.0.jar;

• bpel_SynchroBpelNfeControl_1.0.jar.

• bpel_SynchroNfeR12ReimpressaoReenvio_1.0.jar

(25)

1) Log-in into the BPEL Admin:

(26)

3) Create a new domain where you will deploy the Synchro Services. Check the required data below:

(27)

4) Log-out from BPEL Admin and log-in at the BPEL Console:

5) Select the domain created above and press “Create New Process”:

6) Select the “.jar” file that is located in the folder “BPEL” from the installation kit. After the upload, press “Create”:

(28)

Oracle R12 x DFE Integration Installation procedures