Installing and Configuring E-Business Suite Integration Types for Financial Close Management 11.1.2.3

You must perform the following tasks before creating event monitoring integrations for EBS events.

1. Verify that the Oracle EBS DB’s APPS schema has privileges to write to queue tables.
   This is a MANDATORY step because you run a SQL script on the EBS Oracle DB after creating integration points in FCM. The scripts must be executed on the APPS schema. Therefore, this schema must have the required DB privilege.

2. Verify that the WF_DEFERRED queue agent listener is running.
   The Oracle EBS events make use of Oracle Advance Queuing technology to propagate business event message payloads to external applications. This is achieved through a dedicated queue name WF_DEFERRED. Therefore, it is required to check if this queue listener is running in the EBS instance.
   To verify that the WF_DEFERRED agent is running:
1. Log on to Oracle Applications.
2. Select Workflow Administrator Web Applications responsibility and Workflow Manager under Oracle Applications Manager.
3. Click on the icon next to Agent Listeners.
4. Navigate through the list of Agent Listeners and make sure that Work flow Deferred Agent Listener is running.



3. Perform the following steps for EPM-side WebLogic server configuration of the OracleAppsAdapter (also known as the EBS Adapter):

   1. Verify all managed servers are stopped.
   2. Open a command prompt window.
   3. Navigate to MIDDLEWARE_HOME/wlserver_10.3/server/bin.
   4. Execute the following command setWLSEnv.cmd (for Linux execute setWLSEnv.sh).
   5. Next execute the following command:
      for Windows: Java weblogic.WLST %EPM_ORACLE_HOME%/products/FinancialClose/wlscripts/FCM_EBSConfig_WLS_Script.py
      for Linux: java weblogic.WLST $EPM_ORACLE_HOME/products/FinancialClose/wlscripts/FCM_EBSConfig_WLS_Script.py
      
   6. Provide the connection properties when prompted for EBS Data Source connection details:
      

   Database Name	This is the Oracle DB SID, for example ORCL
   Host Name	The EBS server host name
   Port	This is the Oracle DB port, for example 1521
   Database User Name	APPS
   Database Password	APPS schema password, for example APPS
   Target	EPM SOA managed server, for example soa_server1

   
   

4. Perform the following steps for EPM-side WebLogic server configuration of the DbAdapter (also known as the Database Adapter):

   1. Verify all managed servers are running.
   2. Open a command prompt window.
   3. Navigate to MIDDLEWARE_HOME/wlserver_10.3/server/bin.
   4. Execute the following command setWLSEnv.cmd(for Linux execute setWLSEnv.sh).
   5. Next execute the following command:
      for Windows: java weblogic.Deployer -adminurl t3://<admin-url>:<admin server port> -user <admin user> -password <password> -update -name DbAdapter -plan <db plan location>\FCM_DBAdapter_Plan.xml
      for Linux: java weblogic.Deployer -adminurl t3://<admin-url>:<admin server port> -user <admin user> -password <password> -update -name DbAdapter -plan <db plan location>/FCM_DBAdapter_Plan.xml
      Here is an example:
      java weblogic.Deployer -adminurl t3://localhost:7001 -user weblogic -password welcome1 -update -name DbAdapter -plan C:\Oracle\Middleware\EPMSystem11R1\products\FinancialClose\wlscripts\FCM_DBAdapter_Plan.xml

   This creates the DbAdapter connection factory “eis/DB/financialclose” inside the DbAdapter deployment.

You create event monitoring integration points between EBS by importing integration types from an XML file. To import event monitoring integration types, you create an EBS application in Close Manager and configure the application properties. You then load the XML files for the end user integration types into Close Manager. The event monitoring integration XML file is EBS_GL_EventMonitoring_11123.xml.

1. Login to Close Manager as an administrator.

2. Select Manage Integration Types.

   

3. Select Actions > Manage Applications.

   

4. Select Actions > New.

   

5. Specify the application name and properties. The application values for the EBS application should match the values provide for the "applicationName" element in the integration XML file.

   

   Token Name	Token descriptions
   CONNECTION_FACTORY_APPS	The Oracle Apps Adapter’s javax.resource.cci.ConnectionFactory JNDI name. Value is eis/Apps/Apps.
   DB_SCHEMA	EBS DB Schema Name that has access to the DB queues. Value is APPS

   

6. Click OK.

7. In the Manage Integrations Types dialog box toolbar, click Import.

   

8. Browse to select EBS_GL_Integration_11123.xml, and click Next.

9. Select the integration types for which you want to automatically create task types, and click Next to import the integration types.

10. At the Results screen, click Close.

11. Set the event monitoring integration type parameters:
    The General Ledger Journals Event Monitoring integration types use the same task parameter:

    Name	Type	Required	Order	Hidden
    Batch ID	Text	Yes	1	N

    
    The General Ledger Close Process Period Event Monitoring integration types use the same task parameter:
    

    Name	Type	Required	Order	Hidden
    LedgerID:PeriodName	Text	Yes	1	N

    
    

You run the parameterized SQL script “Subscribe_to_EBS_Events.sql” by connecting to the Oracle EBS DB instance using the APPS schema. Prior to running the SQL script, you must ensure that the event is enabled for subscription.

1. Login to EBS using your administrator credentials.

2. Select the Workflow Administrator role from the left tree.

3. Select the Business Events tab, and find the event in question, for example “oracle.apps.gl.Journals.journal.approve”.

4. When you find the event name, click the Update button.

   

   

5. Run the parameterized SQL script Subscribe_to_EBS_Events.sql, either from SQL Plus or from SQL Developer, by connecting to the EBS instance using APPS schema credentials.

You will find the SQL script “Subscribe_to_EBS_Events.sql” at the following location::
%EPM_ORACLE_HOME%\products\FinancialClose\database\external_sql_scripts.
Note: To subscribe to multiple EBS Events, you have to use a new SQL Worksheet for each Event.

This script takes one parameter: the EBS event name string that is specified in the integration type.
FCM generates a unique key called Consumer ID which is associated with the integration type and event name.
The Consumer ID and event name association can be found at Manage Integration Types > Edit/View Integration Type UI.

6. Confirm the event subscription by running the following SQL query in SQL Developer after connecting to the EBS DB instance:
   select aq$_wf_bpel_qtab_s.name, aq$wf_bpel_qtab_r.rule from apps.aq$_wf_bpel_qtab_s, aq$wf_bpel_qtab_r where apps.aq$_wf_bpel_qtab_s.name = aq$wf_bpel_qtab_r.name;

   This query will give the Consumer ID (NAME column) and event name (RULE column) to which it is related. The subscriptions should resemble the figure below for each event. (Note: If the phase value is greater than 99, change it to a value between 1 and 99).

1. Log on to EBS.

2. Select the General Ledger, Vision Operations (USA) role from the left tree.

3. Select Journals, then Enter and wait for Oracle Applications to start.

4. Click No for the “Find records in this folder?” message.

5. Click New Batch on the Find Journals screen.

   Window (Vision Operation (USA)) is displayed.

6. Enter a unique batch name in the Batch field.

7. Navigate to File menu and click Save to save the batch.

   Note: When you click Save, the cursor needs to be inside the Batch field.

8. Click OK at the “Please enter one or more Journals” message.

9. Click Journals.

10. Enter a unique name in the Journal field.

11. Provide Line entries starting with Line 1. Provide debit and credit values for the accounts.

12. After you finish entering the line values, move the cursor into the Journal field.

13. Navigate to the File menu and click New.

14. Click Yes at the “Do you want to save the changes you made?” prompt.

    Note: This enables you to add multiple Journal entries into the Batch.

15. After finishing all the journal entries for the last Journal, move the cursor into the Journal field.

16. Navigate to the File nenu and click Save to save the last journal entry.

17. Focus on the Batch (Vision Operation (USA)) and select the Batch field.

18. Click the Help menu and select Diagnostics, then Examine.

19. For Oracle password, specify APPS.

20. Enter JE_BATCH_ID in the Field box and click inside the Value box to get the unique Journal batch ID.

    The figure below shows an example value for JE_BATCH_ID.

    

21. Copy this value to Notepad. This batch ID will be used as value in Close Manager for the “EVENT_DATA” parameter of the Event Monitoring task.

22. Perform the following steps in Close Manager:
    1. Create a new schedule in a Pending state.
    2. Create a task for event monitoring task to monitor oracle.apps.gl.Journals.journal.approve.
    3. In the Parameters page, provide a value for EVENT_DATA as the journal batch ID from EBS.
    4. Save the task and set the schedule to state to Open .

23. In EBS, on Batch (Vision Operation (USA)), the Approve button is now enabled for the specified batch. Click Approve to raise the journal approval event.

24. Perform the following steps in Close Manager::
    1. Wait for the task to be set to the Open state. It takes a couple of minutes for SOA to capture the EBS event.
    2. After two or three minutes, click Refresh in Workspace.

Refer to the following document for further information on Journal Batch Approval: http://download.oracle.com/docs/cd/A60725_05/html/comnls/us/gl/journa09.htm#t_ja_submit

1. Log on to EBS.

2. Select the General Ledger, Vision Operations (USA) role from the left tree.

3. Select Journals, then Enter and wait for Oracle Applications to start.

4. Click No for the “Find records in this folder?” message.

5. Click New Batch on the Find Journals screen.

   Window (Vision Operation (USA)) is displayed.

6. Enter a unique batch name in the Batch field.

7. Navigate to the File menu and click Save to save the batch.

   Note: When you click Save, the cursor needs to be inside the Batch field.

8. Click OK for the “Please enter one or more Journals” message.

9. Click Journals.

10. Enter a unique name in the Journal field.

11. Provide Line entries starting with Line 1. Provide debit and credit values for the accounts.

12. After you finish entering the line values, move the cursor into the Journal field.

13. Navigate to the File menu and click New.

14. Click Yes at the “Do you want to save the changes you made?” prompt.

    Note: This enables you to add multiple Journal entries into the Batch.

15. After finishing all the journal entries for the last Journal, move the cursor into the Journal field.

16. Navigate to the File menu and click Save to save the last journal entry.

17. Focus on Batch (Vision Operation (USA)) and select the Batch field.

18. Click the Help menu and select Diagnostics, then Examine.

19. For the Oracle password, specify APPS.

20. Specify JE_BATCH_ID in the Field box and click inside the Value box to get the unique Journal batch ID.

    The figure below shows an example value for JE_BATCH_ID.

    

21. Copy this value to Notepad. This batch ID will be used as value in Close Manager for the “EVENT_DATA” parameter of the Event Monitoring task.

22. Perform the following steps in Close Manager:
    1. Create a new Schedule in a Pending state.
    2. Create a task for event monitoring to monitor oracle.apps.gl.Journals.journal.post.
    3. In the Parameters page, provide a value for EVENT_DATA as the journal batch ID from EBS.
    4. Save the task and set the schedule to state to Open.
    
    
23. Perform the following steps in EBS:
    1. On Batch (Vision Operation (USA)). the Post button is now enabled for the specified batch. Click Post to fire a new EBS Request ID. Note this ID.
    2. Create a task for event monitoring task to monitor oracle.apps.gl.Journals.journal.post.
    3. Examine the status of EBS request by selecting the View menu, then Requests, then Specific Requests.
    4. Specify the Request ID that was noted in step a.
    
    
24. Perform the following steps in Close Manager:
    1. Wait for the task to be set to the Open state. It takes a couple of minutes for SOA to capture the EBS event.
    2. After two or 3 minutes click Refresh in Workspace.

Refer to the following document for further information on Journal Batch Post: http://download.oracle.com/docs/cd/A60725_05/html/comnls/us/gl/conten07.htm#w_conts_post

1. Log on to EBS.

2. Select the General Ledger, Vision Operations (USA) role from the left tree.

3. Select Select Open/Close, then Enter and wait for Oracle Applications to start.

4. At the Find Period dialog box, click the Find button to open the Open and Close Period dialog box.

5. From the list of displayed periods, choose the one you want to close.

6. Select the status column for that period.

7. Click the Status Options button.

   A status list pops up.

8. Selected the Closed status and click OK.

9. Make a note of the period in Notepad.

10. Navigate to the File menu and click Save to save the status.

11. Focus on the status column of the period.

12. Click the Help menu and select Diagnostics, then Examine.

13. For the Oracle password, specify APPS.

14. Enter LEDGER_ID in the Field box and click inside the Value box to get the unique Ledger ID.

    The figure below shows an example value for LEDGER_ID.

    

15. Copy this value to Notepad. You will use Ledger ID as the value in Close Manager for the LedgerID:PeriodName parameter of the Event Monitoring task.

16. Perform the following steps in Close Manager:
    1. Create a new schedule in a Pending state.
    2. Create a task for event monitoring to monitor oracle.apps.gl.CloseProcess.period.close.
    3. In Parameters page, provide a value for LedgerID:PeriodName as “LedgerID:PeriodName” from EBS (for example: 1:Dec-10, where 1 is Ledger ID and Dec-10 is period).
    4. Save the task and set the schedule state to Open.
    
    
17. Perform the following steps in EBS:
    1. Close the Open and Close Period dialog box.
    2. Click OK in the Submit Request Node message box.
    3. Examine the status of the EBS request by selecting the View menu, then Requests, then Specific Requests.
    
    
18. Perform the following steps in Close Manager:
    1. Wait for the task to be set to the Open state. It takes a couple of minutes for SOA to capture the EBS event.
    2. After two or three minutes, click Refresh in Workspace.

Refer to the following document for further information on General Ledger Periods: http://docs.oracle.com/cd/A60725_05/html/comnls/us/gl/openper.htm

1. Log on to EBS.

2. Select the General Ledger, Vision Operations (USA) role from the left tree.

3. Select Open/Close, then Enter and wait for Oracle Applications to start.

4. At the Find Period dialog box, click the Find button to open the Open and Close Period dialog.

5. Copy the period you want to open to Notepad.

6. Focus on the status column of the period.

7. Click the Help menu and select Diagnostics, then Examine.

8. For the Oracle password, specify APPS.

9. Enter LEDGER_ID in the Field box and click inside the Value box to get the unique Ledger ID.

   The figure below shows an example value for LEDGER_ID.

   

10. Copy this value to Notepad. You will use Ledger ID as the value in Close Manager for the LedgerID:PeriodName parameter of the Event Monitoring task.

11. Perform the following steps in Close Manager:
    1. Create a new schedule in a Pending state.
    2. Create a task for event monitoring to monitor oracle.apps.gl.CloseProcess.period.open.
    3. In the Parameters page, provide a value for LedgerID:PeriodName as “LedgerID:PeriodName” from EBS (for example: 1:Dec-10, where 1 is Ledger ID and Dec-10 is period).
    4. Save the task and set the schedule to state to Open .
    
    
12. Perform the following steps in EBS:
    1. Click Open Period.
    2. Select the period to open and click OK to raise the event.
    3. Examine the status of the EBS request by selecting the View menu, then Requests, then Specific Requests.
    
    
13. Perform the following steps in Close Manager:
    1. Wait for the task to be set to the Open state. It takes a couple of minutes for SOA to capture the EBS event.
    2. After two or three minutes, click Refresh in Workspace.

Refer to the following document for further information on General Ledger Periods: http://docs.oracle.com/cd/A60725_05/html/comnls/us/gl/openper.htm

1. Log on to EBS.

2. Select the General Ledger, Vision Operations (USA) role from the left tree.

3. Select Open/Close, then Enter and wait for Oracle Applications to start.

4. At the Find Period dialog box, click the Find button to open the Open and Close Period dialog.

5. Copy the closed period that you want to reopen to Notepad.

6. Focus on the status column of the period.

7. Click the Help menu and select Diagnostics, then Examine.

8. For the Oracle password, specify APPS.

9. Enter LEDGER_ID in the Field box and click inside the Value box to get the unique Ledger ID.

   The figure below shows an example value for LEDGER_ID.

   

10. Copy this value to Notepad. You will use Ledger ID as the value in Close Manager for the LedgerID:PeriodName parameter of the Event Monitoring task.

11. Perform the following steps in Close Manager:
    1. Create a new schedule in a Pending state.
    2. Create a task for event monitoring to monitor oracle.apps.gl.CloseProcess.period.reopen.
    3. In the Parameters page, provide a value for LedgerID:PeriodName as “LedgerID:PeriodName” from EBS (for example: 1:Dec-10, where 1 is Ledger ID and Dec-10 is period).
    4. Save the task and set the schedule to state to Open.
    
    
12. Perform the following steps in EBS:
    1. Click Open Period.
    2. Select the closed period to open and click OK to raise the event.
    3. Examine the status of the EBS request by selecting the View menu, then Requests, then Specific Requests.
    
    
13. Perform the following steps in Close Manager:
    1. Wait for the task to be set to the Open state. It takes a couple of minutes for SOA to capture the EBS event.
    2. After two or three minutes, click Refresh in Workspace.

Refer to the following document for further information on General Ledger Periods: http://docs.oracle.com/cd/A60725_05/html/comnls/us/gl/openper.htm

Perform the steps below to unsubscribe from an EBS business event.

1. Perform the following steps to execute a SQL query to verify that the Consumer ID is subscribed to an EBS Event:
   1. Open SQL Developer.
   2. Connect to the EBS Oracle DB instance using the APPS schema.
   3. Copy and paste the following SQL script:
      select aq$_wf_bpel_qtab_s.name, aq$wf_bpel_qtab_r.rule from apps.aq$_wf_bpel_qtab_s, aq$wf_bpel_qtab_r
      where apps.aq$_wf_bpel_qtab_s.name = aq$wf_bpel_qtab_r.name;
   4. Click the execute SQL button
      A list of Consumer ID and EBS event names is displayed.
   
   
2. If the Consumer ID is found for the event, then perform the steps below to execute a SQL script to unsubscribe:
   1. Open SQL Developer.
   2. Connect to the EBS Oracle DB instance using the APPS schema.
   3. Copy and paste the SQL script provided below step e.
   4. Click the execute SQL button.
   5. Supply values for EventName and Consumer ID when prompted.

      declare
      subscriber sys.aq$_agent;
      begin
      -- remove subscription
      delete from wf_event_subscriptions where event_filter_guid in(
      select guid from wf_events where name = '&EventName')
      and out_agent_guid in ( select guid from wf_agents where name = 'WF_BPEL_QAGENT');
      -- remove subscriber
      begin
      subscriber := sys.aq$_agent('&ConsumerId', NULL, NULL);
      dbms_aqadm.remove_subscriber(queue_name => 'WF_BPEL_Q', subscriber => subscriber);
      exception
      when others then null;
      end;
      end;
      /
      commit;

The Consumer ID will be unsubscribed from the event.

F:\>java weblogic.Deployer -adminurl t3://server_name:7001 -user epm_admin -password ****** -update -name DbAdapter -plan F:\Oracle\Middleware\EPMSystem11R1\products\FinancialClose\wlscripts\FCM_DBAdapter_Plan.xml weblogic.Deployer invoked with options: -adminurl t3:// server_name:7001 -user epm_admin -update -name DbAdapter -plan F:\Oracle\Middleware\EPMSystem11R1\products\FinancialClose\wlscripts\FCM_DBAdapter_Plan.xml <"config-root" C:\Oracle\Middleware\Oracle_SOA1\soa\connectors\plan was not found>
Task 0 initiated: [Deployer:149026]update application DbAdapter on soa_server1. Task 0 completed: [Deployer:149026]update application DbAdapter on soa_server1. Target state: update completed on Server soa_server1

To fix this problem, edit FCM_DBAdapter_Plan.xml:

1. The <config-root> node property C:\Oracle\Middleware\Oracle_SOA1\soa\connectors\plan should be changed to the correct location by locating the SOA_HOME and then changing it to %SOA_HOME%\soa\connectors\plan path. For example, if SOA_HOME is F:\Oracle\Middleware\Oracle_SOA1, then set the value for <config-root> to F:\Oracle\Middleware\Oracle_SOA1\soa\connectors.

2. Re-run the DBAdapter configuration commands.

This scenario could occur if you make use of multiple FCM servers to subscribe to the same EBS DB instance. It is advised to use a single FCM server to subscribe to an EBS DB instance because the Consumer ID is generated based on the integration type ID, which is unique for a given server. If a different FCM server subscribes to an event, with the same Consumer ID, then it is possible that the subscription will not be successful because the EBS Oracle DB will detect it as a duplicate entry.

If you have to use different FCM servers to test the integrations, then it is advised to first unsubscribe the unused Consumer IDs.

1. To verify the EBS Events are not appearing in SOA:
   1. Verify Oracle WebLogic DataSource jdbc/AppsDataSource connects to EBS Oracle DB instance.
   2. Verify that WF_DEFERRED Agent listener is running in EBS Oracle Workflow.
   3. Verify that the financialclose_datasource has SOA server in its target list.
   4. Check the SOA logs for errors of type [ERROR] [] [oracle.soa.adapter].
   
   The OracleAppsAdapter supports Oracle ODL logging and will log errors to SOA server log. Typical errors could be:
   • java.sql.SQLException: ORA-24010: QUEUE APPS.WF_BPEL_Q does not exist.
   • java.sql.SQLException: ORA-25226: dequeue failed, queue APPS.WF_BPEL_Q is not enabled for dequeue.
   
   For these errors, please check if::
   • EBS DB has access permissions for APPS schema.
   • Queue WF_BPEL_Q is present and supports message DQUEUE -ing.
   
   
2. If the above errors are not seen in the SOA server log, use the following procedure to troubleshoot the EBS Oracle DB instance:
   1. Connect to EBS Oracle DB using Oracle SQL Developer with APPS schema credentials (APPS/APPS).
   2. Expand the Other Users node and select APPLSYS user.
   3. Use filter CORRID = 'APPS:<your EBS event name>'.
   4. Check if data is present. If no data exists, reboot the EBS instance.
   5. If data exists in the WF_DEFERRED queue table, then check if data is available in the APPS.WF_BPEL_QTAB queue table.
   6. If data does not exist in APPS.WF_BPEL_QTAB, then ensure Out Agent is set correctly for the event subscription.