Using Seeded Data Extract Services for Oracle
ERP Financial Cloud
ORACLE WHITE PAPER | AUGUST 2017
Table of Contents
Overview 3
List of Seeded Data Extracts 4
High-level Data Extract Flow 6
How to Invoke Financial Data Extracts 7
High-level Flow Steps 7
Flow Automation using the Oracle ERP Integration Web Service 8
Seeded Extract Parameter Details 9
Sample Payload of Seeded Extract 10
Advanced Features 11
How to customize data extracts 11
Incremental Extracts 12
Appendix 1: Execute from Fusion Applications UI 16
Generate Base Extract 16
USING EXTERNAL DATA INTEGRATION SERVICES FOR ORACLE ERP CLOUD
Overview
The Financials Data Extract empowers business enterprises with the capability to extract financials data from the Oracle ERP
Cloud and seamlessly integrate it with the on-premises systems, legacy systems, and other cloud systems. The seeded
financial data extracts have been introduced from R12.
The data extracts are provided in a XML or CSV format for end-user review and direct automated integration with other
systems. One very common business scenario in practice is to leverage the extract capabilities to directly import the data files
extracted from the Oracle ERP Cloud to legacy or third-party partner systems. In turn, those legacy or third-party partner
systems will perform very specialized niche business functions based on unique legal or industry requirements such as
generating statutorily prescribed tax return forms for electronic filing with local country tax authorities.
Basic Seeded Data Extract includes the following features:
• Users can extract a snapshot of data as per original report.
• All parameters as per the original report are available for user to extract data.
• Basic extract includes Descriptive Flexfield (DFF) and Global Descriptive Flexfield (GDF) data where applicable.
• Supports CSV output format for efficient and seamless data integration with existing on-premise systems, legacy systems, or
other cloud systems.
• Output data file is compressed (.ZIP) and stored in UCM for user to download.
3 | USING EXTERNAL DATA INTEGRATION SERVICES FOR ORACLE ERP CLOUD
List of Seeded Data Extracts
The following seeded extracts are supported starting R12:
Application Seeded Extract Name Original Report Name
Payables Payables Transactions Payable Invoice Register
Payables Payments Payment Audit by Voucher
Number
Receivables Receivables Transactions Print Receivables Transactions
Receivables Receivables Adjustments Print Adjustments
Receivables Receipt Analysis Receipt Days Late Analysis
Receivables Receivables Billing History Billing History
General Ledger Journals Journals Batch Summary
General Ledger Trial Balance Trial Balance
Tax Financial Tax Financial Tax Register
The following business objects are included in preceding seeded extracts table:
Application Business Objects Covered
4 | USING EXTERNAL DATA INTEGRATION SERVICES FOR ORACLE ERP CLOUD
Payables Payables Standard Invoice, Supplier, Payables Invoice Hold,
Payables Prepayment, Payables 1099 Tax Reporting, Transaction
Tax, Payables Withholding Tax, Payables Payment
Receivables Receivables Invoice, Receivables Adjustment, Receivables
Receipt, Receivables receipt method, Receivables Balance forward
bill, Customer Account, Receivables Bills receivables, Receivables
Transaction Balance summary
General Ledger Journal, Journal Category, Journal Source, General Ledger
Account, General Ledger Actual Balance, Chart of Accounts
Mapping, Accounting Period
Tax Payables Invoice Batch, Payables Tax Reporting Entity, Payables
Standard Invoice, Transaction Tax, Receivables Invoice, General
Ledger Account, Journal
5 | USING EXTERNAL DATA INTEGRATION SERVICES FOR ORACLE ERP CLOUD
High-level Data Extract Flow
The following high-level steps describe how to execute seeded extracts:
1. User launches “Generate Data File for Extract” job with respective parameter values and execution mode.
2. ERP Cloud executes the respective BIP report.
3. Upon completion, a CSV file is generated in a compressed format and uploaded to UCM.
4. User downloads the file using web services.
The following provides the functional overview of the flow:
6 | USING EXTERNAL DATA INTEGRATION SERVICES FOR ORACLE ERP CLOUD
How to Invoke Financial Data Extracts
A new ESS job Generate Data File for Extract is introduced to trigger the data extract by extract name and execution
mode. The execution can be automated through the ERP integration service or user can navigate through the UI.
High-level Flow Steps
• Flow automation through ERP Integration Service (Export Bulk Data)
– Supports both seeded and customized seeded Extracts.
– Supports the CSV format for system-to-system integration scenarios.
– Supports pull and push integration patterns.
• UI - Oracle ERP Cloud Scheduled Processes page
– Navigate to Scheduled Process > Generate Data File for Extract.
– Select the Extract Name & Execution Mode and respective parameters.
– Submit the Extract.
7 | USING EXTERNAL DATA INTEGRATION SERVICES FOR ORACLE ERP CLOUD
Flow Automation using the Oracle ERP Integration Web Service
The Oracle ERP Integration Service is an external web service that provides robust web service operations, such as
supporting the bulk import of data into the Oracle ERP Cloud, the bulk export of data from the Oracle ERP Cloud, and key
value-added operations to retrieve files, and purge interface and error data periodically. For data extract, bulk export
operation can be implemented to automate end-to-end flow.
The following diagram depicts the high-level flow:
The Oracle ERP Integration Service includes the exportBulkData operation to automate the orchestration flow.
The following table lists the parameters for the exportBulkData operation:
Parameter Description Parame Mandat
Name ter ory
(In/Out)
Job Name It contains the job package name Job definition name (comma separated). For Financial IN Yes
Data Extract, use
oracle/apps/ess/financials/commonModules/shared/common/outbound,FinOutboundProce
ss
Parameter Parameters of the job – comma separated. Refer to table 3 for seeded extracts and table 4 IN Yes
List for custom extracts.
8 | USING EXTERNAL DATA INTEGRATION SERVICES FOR ORACLE ERP CLOUD
Notificatio A two-digit number that determines how and when a notification is passed for the status of IN No
n Code the export job. Refer to the Customer Connect post and How-To document for more
details.
Callback The callback URL of the web service you implemented for receiving the status upon job
URL completion.
IN No
Job Optional parameters, comma separated. IN No
Options
To enable data file encryption, you must provide the following options:
FileEncryption=PGPUNSIGNED or PGPSIGNED
FA_ALIAS=<ERP Cloud Key Alias Name>
CUSTOMER_ALIAS=<Customer Key Alias Name>
Example:FileEncryption=PGPUNSIGNED,FA_ALIAS=ERP_CLOUD_KEY,CUSTOMER_A
LIAS=CUSTOMER_ERP_KEY
Response The response code that returns the request identifier of the export job.
Code
OUT
Table 2 - exportBulkData Payload Details
Note: For detailed information on exportBulkData operation, refer to the Customer Connect post and How-To document.
Seeded Extract Parameter Details
The list of parameters for the seeded extracts is as follows:
Parameter Details
Field
1 Interface ID of the supported extract. Refer to table 1 for the interface ID.
2 Seeded extract job package and name separated by “;”
Example:
/oracle/apps/ess/financials/commonModules/shared/common/outbound;PayablesTransactionsExtract
Note: Job package name is same for all Financial Data Extracts
(/oracle/apps/ess/financials/commonModules/shared/common/outbound). Refer to table 1 for the job name.
3 It is always BIPREPORT
9 | USING EXTERNAL DATA INTEGRATION SERVICES FOR ORACLE ERP CLOUD
4 Extract Mode Type
FULL_EXTRACT is only supported option
5 Respective report parameters start from here
Table 3 - Seeded Extracts Parameter Details
Sample Payload of Seeded Extract
The following illustration highlights a sample request payload of the exportBulkData operation for seeded extract:
<soap:Body>
<ns1:exportBulkData
xmlns:ns1="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegra
tionService/types/">
<ns1:jobName>oracle/apps/ess/financials/commonModules/shared/common/outbound,FinOutbound
Process</ns1:jobName>
<ns1:parameterList>92,/oracle/apps/ess/financials/commonModules/shared/common/outbound;P
ayablesTransactionsExtract,BIPREPORT,FULL_EXTRACT,#NULL,204,#NULL,#NULL,#NULL,2014-01-
02,2014-01-03,Jan-
14,N,N,1,STANDARD,#NULL,#NULL,FULL_EXTRACT,#NULL,#NULL,#NULL,PayablesTransactionsExtract
,#NULL</ns1:parameterList>
<ns1:jobOptions></ns1:jobOptions>
<ns1:callbackURL>#NULL</ns1:callbackURL>
<ns1:notificationCode>#NULL</ns1:notificationCode>
</ns1:exportBulkData>
</soap:Body>
Note: For callback response, refer to the Customer Connect post.
10 | USING EXTERNAL DATA INTEGRATION SERVICES FOR ORACLE ERP CLOUD
Advanced Features
How to customize data extracts
To meet additional requirements, customize the seeded extracts by performing the following steps:
1. Select the seeded data extract from the execution mode. Register custom enterprise scheduler job to create a
copy of seeded extract.
2. Create a copy of the original .xdo and .xdm files in the Business Intelligence (BI) server under the custom
folder.
Note: The custom created .xdo file must point to the custom created .xdm file.
3. Add or remove columns. You can also extend the model.
User can create a custom report in BI Publisher (BIP), or clone one of the seeded extracts and customize it based on their
requirements.
The following are the prerequisites to run the custom extract:
1. Create a copy of original .xdo and .xdm files in BI server under Custom folder.
2. Provide the names of these custom created .xdo and .xdm file same as the name you gave for the custom
extract on the UI.
Note: Custom created .xdo file should be made to point to custom created .xdm file.
The parameter list of the custom extracts is as follows:
Parameter Details
Field
1 Is always #NULL
2 Name of the BIP report
Example: /Financials/Transaction Tax/FinancialTaxRegister.xdo
3 Is always BIPONLY
4 Is always #NULL
5 Name of the report file
Example: FinancialTaxRegister
6 The report parameters starts from here
Table 4 - Custom Extracts Details
The following illustration highlights a sample request payload of the exportBulkData operation for the custom BIP report:
11 | USING EXTERNAL DATA INTEGRATION SERVICES FOR ORACLE ERP CLOUD
<soap:Body>
<ns1:exportBulkData
xmlns:ns1="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegra
tionService/types/"> <ns1:jobName>oracle/apps/ess/financials
/commonModules/shared/common/outbound,FinOutboundProcess</ns1:jobName>
<ns1:parameterList>#NULL,/Financials/TransactionTax/FinancialTaxRegister.xdo,BIPONLY,#NU
LL,FinancialTaxRegisterBIPOnly,2000,204,#NULL,#NULL,TAX,TRANSACTION,ALL,2010-01-20,2010-
01-
26,#NULL,#NULL,#NULL,#NULL,#NULL,#NULL,#NULL,#NULL,#NULL,#NULL,#NULL,#NULL,#NULL,#NULL,#
NULL,#NULL,#NULL,TRANSFERRED,#NULL,#NULL,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,BOTH,#NULL,#NUL
L,#NULL,Y,RXZXPFTR,#NULL,#NULL,#NULL</ns1:parameterList>
<ns1:jobOptions></ns1:jobOptions>
<ns1:callbackURL>#NULL</ns1:callbackURL>
<ns1:notificationCode>#NULL</ns1:notificationCode>
</ns1:exportBulkData>
</soap:Body>
</soap:Envelope>
Incremental Extracts
The financial data extract supports extracting data incrementally after the based extract is completed. The incremental
extracts are tracked in context of distinct combination of fixed dimensions such as Business Unit, Legal Entity, etc. These
are the following dimensions for seeded extracts:
Application Seeded Extract Name Fixed Dimensions
Payables Payables Transactions BU, Invoice Type, Accounting Period
Payables Payments BU, Document Sequence
Receivables Receivables BU, Transaction Class
Transactions
Receivables Receivables BU
Adjustments
12 | USING EXTERNAL DATA INTEGRATION SERVICES FOR ORACLE ERP CLOUD
Receivables Receipt Analysis BU
Receivables Receivables Billing BU
History
General Ledger Journals Ledger, Data Access Set, Currency Code
General Ledger Trial Balance Ledger, Data Access Set, Currency Code,
Accounting Period
Tax Financial Tax Reporting context, Legal Entity, Tax type,
Source
The following diagram depicts the parameters of payables transactions extract:
13 | USING EXTERNAL DATA INTEGRATION SERVICES FOR ORACLE ERP CLOUD
File prefix – user defines the name of the output file
Fixed Dimensions – These dimensions track incremental logic. In previous diagram, it is tracked by Business Unit,
Invoice Type and Accounting period.
The following is the high-level flow for incremental extracts:
14 | USING EXTERNAL DATA INTEGRATION SERVICES FOR ORACLE ERP CLOUD
After based extract is completed successfully, incremental extract can be configured for up to next 30 days of data. You can
execute it multiple times until the data is validated. Once the user validates the data and acknowledges the extracted data,
the system updates the last extracted date. The next execution is based on the new last extracted date.
You can also rerun the last acknowledged incremental extract by using the “Reprint” option. The reprint is based on same
fixed dimensions of the pervious extract.
15 | USING EXTERNAL DATA INTEGRATION SERVICES FOR ORACLE ERP CLOUD
Appendix 1: Execute from Fusion Applications UI
In addition to the automated approach, extract process can also be executed through the Oracle ERP Cloud user interface.
Perform the following steps to execute the seeded extract process through the user interface:
1. From the Oracle ERP Cloud home page, navigate to the Navigator > select Tools > Scheduled Processes.
2. Click Schedule New Process.
3. Search for the Generate Data File for Export process name and select it.
4. Select Extract Name and mode.
5. Enter respective parameters value.
6. Click Submit.
7. After the job is completed, click on the report and export the data file.
The following image is the sample of executing base Payment extracts:
16 | USING EXTERNAL DATA INTEGRATION SERVICES FOR ORACLE ERP CLOUD
Oracle Corporation, World Headquarters Worldwide Inquiries
500 Oracle Parkway Phone: +1.650.506.7000
Redwood Shores, CA 94065, USA Fax: +1.650.506.7200
CONNECT W ITH US
blogs.oracle.com/oracle
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. This document is provided for information purposes only, and the
facebook.com/oracle
contents hereof are subject to change without notice. This document is not warranted to be error-free, nor subject to any other
twitter.com/oracle warranties or conditions, whether expressed orally or implied in law, including implied warranties and conditions of merchantability or
fitness for a particular purpose. We specifically disclaim any liability with respect to this document, and no contractual obligations are
oracle.com
formed either directly or indirectly by this document. This document may not be reproduced or transmitted in any form or by any means,
electronic or mechanical, for any purpose, without our prior written permission.
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and
are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are
trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group. 0917
