Before You Install the Recipe

You must perform the following configuration tasks on your Oracle ERP Cloud instance and sFTP server in order to successfully connect to these external systems using Oracle Integration and export data in bulk.

Configure Oracle ERP Cloud

To access Oracle ERP Cloud from Oracle Integration, you’ll require a separate user account on Oracle ERP Cloud. Additionally, if no report and/or a job for the report is created in Oracle ERP Cloud, you have to create a report and job.

Log in to Oracle ERP Cloud as an Administrator and perform the following tasks.

1. Create a user account for Oracle Integration. Make a note of the user name and password you set for the account. You’ll use the credentials of this user account to connect to Oracle ERP Cloud from Oracle Integration.
2. Assign the Integration Specialist role to the user account.
   For more information, see Assign Required Roles.
3. Create a report.
   1. Log in to Oracle ERP Cloud.
   2. On the Home page, click the arrow next to the user role, and then select Setup and Maintenance under Administration.
   3. In the Search Tasks field, enter Ledger and click the search icon (magnifying glass) to search for the task.
   4. In the resulting list, select Manage Primary Ledgers.
   5. Select Manage Primary Ledgers again from the task list.
   6. Note down the Ledger ID. You'll need it to configure the report in step 4.
   7. Now, log in to BI Publisher at https://<yourinstance>/xmlpserver/servlet/catalog using a valid user name and password
   8. In the Catalog window, click the "+" sign on the top-left of the window, and then select Data Model.
   9. In the Data Model window, click the Diagram tab, and then click the "+" sign.
   10. In the resulting menu, select SQL Query.
   11. In the New Data Set - SQL Query window, enter the following details and then click OK.
       1. In the Name field, enter a suitable name.
       2. In the Data Source drop-down field, select ApplicationDB_FSCM.
       3. In the Type of SQL drop-down field, select Standard SQL.

       4. Add the following SQL query:

          SELECT
          
          l.ledger_id, l.name,
          
          c.code_combination_id, c.account_type, segment1,
          
          segment2, segment3, segment4, segment5, segment6, segment7, segment8, segment9, segment10,
          
          c.financial_category FROM
          
          gl_ledgers l, gl_code_combinations c
          
          WHERE
          
          
          
          c.detail_posting_allowed_flag = 'Y' AND c.summary_flag = 'N'
          
          AND c.enabled_flag = 'Y'
          
          
          
          and sysdate between nvl(c.start_date_active, sysdate) and nvl(c.end_date_active, sysdate)
          
          and c.chart_of_accounts_id = l.chart_of_accounts_id and l.ledger_id = < ledger_ID >
          
          order by 1

       Note that ledger ID must be the ledger ID that you noted down in step 3f.

   12. Select the Data tab, and then click View.
   13. In the Data Model pane, click Parameters.
   14. In the Parameters panel, click the + sign to add a parameter. Enter the Name, Data Type, and Value for the parameter. Add as many parameters as you want, and then click Save.
   15. Select a folder to save your data. Enter a name and description, and then click Save.
       You get a confirmation that the data has been saved.
   16. Click Save as Sample Data.
   17. Click OK in the resulting dialog.
   18. Click Create Report.
   19. In the Create Report wizard:
       1. Select Use Data Model. In the Data Model field, browse and select the data model that you saved. Click Next.
       2. Select the Page Option and Layout, and then click Next.
       3. Drag and drop the data that you want to include in the report from the left to the right pane. Click Next.
       4. Select the Show Grand Totals Row check box, and click Preview Report to view the report. Click Next.
       5. Click Finish.
   20. Select a Catalog folder where you want the report to be saved. Enter a name and description and click Save.
   The report is created. If required, you can change the output format of the report by clicking View Report and choosing an available format (for example: PDF, HTML, RTF and so on).
4. Create a job for the report.
   1. On the Oracle ERP Cloud Home page, click the arrow next to the user role, and select Setup and Maintenance.
   2. Click the Tasks icon on the side menu, and then select Search from the menu.
   3. In the search bar, search for scheduler job.
   4. Select the required Task List.
   5. Select the required task in the task list.
   6. Click the + sign to create a new job.
   7. Enter information in the required fields.
      • Display Name: Enter a display name.
      • Name: Enter a name.
      • Path: Enter the folder path to store the job definition.

        Note that this folder is created in /oracle/apps/ess/custom/<Job Application Name>. Where, job application name should be FscmEss.

      • Job Type: Select BIPJobType.
      • Report ID: This is the path to the location where your job is saved.
   8. In the Parameters panel, click the + sign to add a parameter. Enter the Name, Data Type, and Value for the parameter. Add as many parameters as you want and then click Save.

      Note that the parameters added here must be same as those that were added in step 3n while creating the report.

   9. Click Save and Close.

   A message confirms that changes have been saved.

Access Your FTP Server

Perform the following tasks on your FTP server.

1. Log in to the server using your user name and password through an FTP client; for example, FileZilla.
2. Create an output folder to write files with the bulk data. Note down the path of the directory.