1. Implementing Accounting Hub
  2. Upload Transaction Data for Accounting Hub

Upload Transaction Data for Accounting Hub

You can import transaction data from a source system that has accounting entries created in Accounting Hub. After completing the source system's registration and the accounting rules' configuration, you can upload the transaction using a spreadsheet.

This figure illustrates the upload transaction data processes. You can upload the transaction data manually or through an automated process.

This figure illustrates the upload transaction data processes. You can upload the transaction data manually or through an automated process.

Manually Upload Transaction Data

To upload the transaction data manually, follow these sequential steps:

  1. Download the transaction data template using the Manage Subledger Application task.

  2. Save the file on a local drive.

  3. Extract the three files contained in the downloaded template.

    1. Metadata.txt

      • The metadata file contains key information about the source system.

    2. XlaTrxH.csv

      • This file contains the header-level columns for the transaction data that can be populated.

      • The column names match the short name of the sources that were included in the Transaction Information worksheet during the registration of your source system.

      • One column that's available in the header and line file is the Transaction Number. The Transaction Number links the header and its corresponding lines information.

    3. XlaTrxL.csv

      • This file contains the line-level columns for the transaction data that can be populated.

      • The column names match the short name of the sources that were included in the Line Information worksheet during the registration of your source system.

  4. Populate the header and line CSV files.

    Note: Both XlaTrxH.csv and XlaTrxL.csv files may contain column names that aren't used by the subledger, such as Character9, Date2, Long Character1 and so on. You can leave the unused column blank when populating transaction data in these files.

  5. Select and compress the three files.

    • The file names must remain the same as the downloaded files.

    • As a best practice, Oracle recommends that the .zip file has a unique name that includes a subledger source and date stamp.

    • The .zip file must begin with XlaTransaction.

    • We recommend you suffix the file with the subledger source name and date to identify the file uniquely. For example, XlaTransactionRetailLoan050417.zip.

  6. Upload the .zip file into Universal Content Management.

    Caution: You must assign the Accounting Hub Integration Duty Role to a user-defined job role to perform this step. Also, you must ensure that the user is also assigned the General Accounting Manager job role. Otherwise, you must add the Upload Data for Accounting Hub Import role to the job role.Alternatively, you can assign the Subledger Accounting Duty and Subledger Accounting Reporting Duty roles to any of the following general ledger job roles:

    • Navigate: Navigator > Tools > File Import and Export.

    • Click Create.

    • Browse for the compressed file.

    • Select account: fin/fusion/AccountingHub/Import.

    • Save and Close.

    • View the file upload results in the Search Results region.

  7. Submit the Import Accounting Transactions process.

    • Navigate: Navigator > Scheduled Processes.

    • This process imports accounting transactions from Universal Content Management, creates accounting entries and posts the journal entries to the general ledger.

    Optionally, you can choose to upload the transaction data to the interface tables, import transaction data from the interface tables, and create accounting as separate processes. To do this, you enable the Flexible Configuration of Accounting Hub Cloud Service feature. Refer to the Advanced Features for Importing Transaction Data to Accounting Hub topic for details.

If there are invalid entries, query journal entries with Invalid status from the Review Journal Entries task, view the transaction, and correct the errors directly. Refer to the Correct Accounting Hub Transaction Data for Invalid Entries topic for details.

Note: Error correction is dependent on the dependent on the Update Subledger Transactions (XLA_UPDATE_SUBLEDGER_TRANSACTIONS_PRIV) privilege. You can remove or add the privilege per your company's policy.

You can also run the Accounting Hub Maintenance process with the Purge invalid transactions mode to delete invalid transaction data along with the resulting invalid subledger journal entries, modify the data in CSV files and import them again. Refer to Accounting Hub Maintenance for more details.

By default, uploaded transaction data are kept in the Accounting Hub Cloud for 90 days. Transactions that are used to create multiperiod accounting entries are kept until the end date of the multiperiod duration. You can change the transaction retention period's duration to any value between 30 days and 458 days. To change the accounting retention period, navigate to the Manage Subledger Accounting Lookups page, and specify a value for the ORA_XLA_AHC_RETENTION_DAYS lookup type.

Automated Processing Options

Automated processing options are available to bring the transaction data files from the registered source systems into Accounting Hub. This includes data from other external sources, such as other company-specific applications, transaction applications, or legacy applications. Optionally, data transformation processing may be required for data sourced from PaaS or other external files.

  • Transaction data in CSV format are imported into Accounting Hub using the Universal Content Management with one of three available integration options:

    • Oracle WebCenter Content Document Transfer Utility

    • Oracle UCM Web Services

    • ERP Integration Service, upload to UCM

  • Import the transaction data using the Import Accounting Transactions process.

  • The Create Accounting process is automatically called, resulting in the creation of journal entries.

For more information about automated processing options, see the following resources in the Oracle Help Center:

  • Oracle Fusion Middleware Developer's Guide for Content Server: Auditing Web Services

  • Oracle Integration Cloud Service

  • Oracle Accounting Hub Best Practice guide

Upload Encrypted Data

You can use data encryption to secure data files sent to the Accounting Hub Cloud Service. In this case, the Import Accounting Transactions process decrypts the data files using the Cloud private key and then starts the load and import process.

Here are the steps to upload encrypted transaction data:

  1. On the Certificates tab of Security Console, generate a PGP public and private key pair. Then, export the Cloud public key.

  2. In an external encryption utility, you generate the customer side key pair by using the Cloud public key.

  3. Export the customer public key.

  4. On the Certificates tab of Security Console, import the customer public key.

  5. In an external encryption utility, encrypt the transaction data ZIP file using the Cloud public key.

  6. When uploading the encrypted .zip file into UCM, provide encryption details in the Comments field in the following format:

    FileEncryption=PGPUNSIGNED;FA_ALIAS=CLOUD_KEY;CUSTOMER_ALIAS=YOUR_KEY

    Where, CLOUD_KEY is the Cloud public key alias, YOUR_KEY is the customer public key alias.

    Note: You can provide the comments using the AppendFileComment operation of the ERP Integration Service or through WebCenter Content Server.

Testing

Once the setup is complete, testing should be comprehensive to ensure that all accounting is correctly generated.

At a minimum, this should include testing for the following:

  • Sources are available for creating subledger journal entries.

    • Review available sources from the Manage Sources user interface.

    • Confirm that when you configure accounting rules, sources are available for use.

  • Subledger journal entries accurately reflect the accounting rules.

We also suggest that you test the following:

  • Subledger journal entries contain the appropriate dates, amounts, descriptions, and accounts.

  • Conditions used to determine journal line rules, account rules, or descriptions are valid.

  • Subledger journal entries are successfully transferred and posted to general ledger and reflected in subledger accounting balances.

Note: To perform detailed diagnostics on the underlying transaction sources used to accounting journal entries, follow the Subledger Accounting diagnostic framework business process flow. See the Diagnose Subledger Accounting Event Data: Explained topic for details.

Gain Insight Using Journal Entries

Once you have the accounted subledger journal entries in Accounting Hub, you can use the posted journal entries to gain insightful information.

  • To streamline period close activities, perform a reconciliation of subledger journal entries with the posted journal entries in the GL or the source system, on a regular basis.

    • Review the supporting references report that tracks specific accounts in the subledger. Reconcile with the same account in the general ledger.

    • The Supporting References report is a user-defined analysis using OTBI subject areas; Subledger Accounting - Journals Real Time, and Subledger Accounting - Supporting References Real Time.

  • Review the accounting reports to gain insight on your current business performance with the configured analytical metrics, such as supporting references.

  • Perform queries on specific accounts in the general ledger and drill down to its corresponding subledger journal entries and transaction data.

Related Topics