Import a Payment Request into Oracle ERP Cloud

Import a Payment Request into Oracle ERP Cloud

Use this recipe to import a payment request from an FTP server to Oracle ERP Cloud.

Note: This recipe is available as FTP — Oracle ERP Cloud | Import Payment Requests in the Integration Store. Oracle provides this recipe as a sample only. The recipe is meant only for guidance, and is not warranted to be error-free. No support is provided for this recipe.

Overview

This recipe imports an interface file containing payment requests from an FTP server to Oracle ERP Cloud according to a schedule specified in Oracle Integration.

In Oracle ERP Cloud, a payment request is used to make a one-time payment (without creating a supplier record) to external suppliers or parties. When a payment request (transaction) is imported into Oracle ERP Cloud, the payees, parties, and bank accounts are automatically created in the application, thereby simplifying the task of making the payment. In addition, the transactions are validated automatically, eliminating the need for manual intervention.

As part of this recipe, Oracle provides an example file structure that you can use to load a payment request into Oracle ERP Cloud.

To use the recipe, you must install the recipe package and configure the connections and other resources within the package. Subsequently, you can activate and run the recipe manually or specify an execution schedule for it. When triggered, the recipe loads a payment request from a specified FTP server to Oracle ERP Cloud.

System and Access Requirements

Recipe Schema

This section provides an architectural overview of the recipe.

The primary integration flow (Oracle FTP ERP Cloud Payment Request), when triggered by an execution schedule or a manual submission, queries the FTP server for a payment request file (.zip). If a file is found, the integration flow processes the file. Initially, the flow maps the file’s layout to the layout required by Oracle ERP Cloud, and then it imports the payment requests in bulk through the ERP Adapter.

After importing the payment request, Oracle ERP Cloud makes a callback to the secondary integration flow (Oracle FTP ERP Cloud Payment Request Callback), which downloads the log files from Oracle ERP Cloud and places them on the FTP server.

Recipe Schema

Description of the illustration recipe-schema.png

Before You Install the Recipe

You must perform the following configuration tasks on your FTP server and Oracle ERP Cloud instances in order to successfully connect to these external systems using Oracle Integration and load payment requests.

Access Your FTP Server and Upload the Files

Obtain an sFTP server and ensure that you’re able to access it.

  1. Log in to the server using your user name and password through an FTP client; for example, FileZilla.

  2. Create a directory on the server from which to read the files. Note the path of this directory.

  3. Upload a payment-request file (.zip) into the directory you’ve created. You must name the file as apPaymentRequest.zip.

    Note: The .zip file must contain two files by the name, apPaymentRequest.properties and apPaymentRequest.csv. For details on the structure of these files, see Appendix A: Payment Request File Definition.

Configure Oracle ERP Cloud

To access the Oracle ERP Cloud instance from Oracle Integration, you’ll require a separate user account on Oracle ERP Cloud.

Log in to your Oracle ERP Cloud instance 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 following roles to the user account. For more information on these roles, see Assign Required Roles to an Integration User.

    • Integration Specialist

    • AttachmentsUser

    • SOAOperator

    • FND_MANAGE_CATALOG_SERVICE_PRIV

    • Payables Invoice Creation

Install and Configure the Recipe

On your Oracle Integration instance, install the recipe package to deploy and configure the integration and associated resources.

  1. On the Oracle Integration home page, scroll to the Accelerators & Recipes section.

  2. Click Search All.

  3. Find the recipe package that you want to install.

  4. Select the package, and then click the Install Install icon icon.

  5. After the package is installed, click the Configure Configure icon icon on the recipe card to configure the resources deployed by the package.

    The Configuration Editor page opens, displaying all the resources of the recipe package. Configure the following resources before you activate and run the recipe.

Configure the FTP Connection

  1. On the Configuration Editor page, select FTP Connection.

  2. Click Edit Edit icon.
    The connection configuration page appears.

  3. In the Connection Properties section, enter the following details.

    Field Information to Enter
    FTP Server Host Address Enter the host address of your sFTP server.
    FTP Server Port Enter 22.
    SFTP Connection Select Yes from the list.

  4. In the Security section, enter the following details.

    Field Information to Enter
    Security Policy Select FTP Public Key Authentication.
    User Name Enter the user name to connect to your sFTP server.
    Private Key File Upload the private key file for your sFTP server.

  5. Click Save. If prompted, click Save for a second time.

  6. Click Test to ensure that your connection is successfully configured. In the resulting dialog, click Test again.
    A confirmation message is displayed if your test is successful.

  7. Click Back Back icon to return to the Configuration Editor page. Click Save again if prompted.

Configure the Oracle ERP Cloud Connection

  1. On the Configuration Editor page, select Oracle ERP Cloud Connection.

  2. Click Edit Edit icon.
    The connection configuration page appears.

  3. In the Connection Properties section, enter the Oracle ERP Cloud host name. For example: https://your_domain_name.fa.DC.oraclecloud.com.

  4. In the Security section, enter the following details.

    Field Information to Enter
    Security Policy Select Username Password Token.
    User Name Enter the user name of the account created for Oracle Integration on Oracle ERP Cloud. See Configure Oracle ERP Cloud.
    Password Enter the password of the account created for Oracle Integration on Oracle ERP Cloud.

  5. Click Save. If prompted, click Save for a second time.

  6. Click Test to ensure that your connection is successfully configured.
    A confirmation message is displayed if your test is successful.

  7. Click Back Back icon to return to the Configuration Editor page. Click Save again if prompted.

Configure the Lookup Table

Edit the lookup table to specify the FTP server directory from which the file must be read, the name of the file to be read, and an email ID to receive error messages.

  1. On the Configuration Editor page, select the lookup table, and click Edit Edit icon.

  2. Enter the following information.

    Property Name Information to Enter
    FTPImportFileName Specify the name of the file to be read from the FTP server.
    Enter apPaymentRequest. See Access Your FTP Server and Upload the Files.
    emailErrorNotificationTo Enter an email ID of your choice.
    FTPImportDirectory Specify the path of the directory on your FTP server from where the file must be read. For example: /home/user2/FTP.

  3. Click Save. If prompted, click Save for a second time.

  4. Click Back Back icon to return to the Configuration Editor page.

Activate and Run the Recipe

After you’ve configured the connections and the lookup table, activate the recipe package and run it.

  1. On the Configuration Editor page, click Activate in the title bar. In the Activate Package dialog, click Activate again.
    A confirmation message is displayed informing that the integrations have been submitted for activation. Refresh the page to view the updated status of the integrations.

  2. Run the recipe.

    1. On the Configuration Editor page, select the primary integration flow (Oracle FTP ERP Cloud Payment Request).

    2. Click Run Run icon, then click Submit Now.
      You’ve now successfully submitted the integration for execution.

      Note: You can also schedule this integration to run at a date, time, and frequency of your choosing. See Define the Integration Schedule.

  3. Monitor the execution of the integration flows in Oracle Integration.

    1. On the Configuration Editor page, select the primary integration flow.

    2. Click Run Run icon, then click Track Instances.

    3. On the Track Instances page, you’ll see the integration flows of the recipe being triggered and executing successfully.
      The recipe now imports a payment request from your FTP server to Oracle ERP Cloud.

  4. Check for the new payment request on Oracle ERP Cloud.

    1. Log in to the Oracle ERP Cloud instance using a separate account, for example, as a user with the Accounts Payable Invoice Supervisor/Accounts Payable Manager/Accounts Payable Specialist role.

    2. Navigate to the Invoice Dashboard screen.

    3. In the Invoice Number search field, enter the value of the Invoice Number field that’s in your interface file, and click Search. The imported payment-instruction invoice is displayed.

      Payment Invoice

  5. On your FTP server, check the log files from Oracle ERP Cloud.

    1. Log in to the server and navigate to your directory.
      You’ll find a file with the following name: apPaymentsRequest.zip_dateandtimr.zip.

    2. Download this file from the FTP server to your local system.

    3. Unzip the file and inspect the log files present within.

      ERP Cloud Log Files

Related Documentation

Appendix A: Payment Request File Definition

This section describes the file layout used by the primary integration flow (Oracle FTP ERP Cloud Payment Request) of the recipe. This integration flow is configured to look for a .zip file on your sFTP server. The .zip file must contain the following two files:

Definition: apPaymentRequest.properties

This file tells Oracle ERP Cloud how to process and validate the payment requests. This file is in the .csv format.

Parameter Description
Program Location oracle/ apps/ess/financials/payables/invoices/transactions/
Program APXPRIMPT
Interface The file name to be loaded, this will be the the name placed within the FTPImportFileName lookup without the .zip suffix. For example: apPaymentRequest.
Business Unit Setup and Maintenance/Manage Business Unit.
Accounting Date Enter an accounting date. If you don’t enter a date, the Accounting Date is based on the Accounting Date Basis option.
Oracle_Int1 Hard-coded internal value set to 1000.
Source For Payment Requests, this is always ONE_TIME_PAYMENTS.
Import Set Name of an import set to limit the import.
Purge

Yes, for Payables to delete records that match the Source and Group ID of this import.

No, to keep all the records except the rejected records.
Summarise Enter whether to summarize the audit portion of the Payables Payment Request Interface Import Report.
Ledger Setup and Maintenance/Manage Primary Ledgers.
Invoice Group Enter the name of an invoice group.
Oracle_Int2 Hard-coded internal value set to 1.

Example Values: apPaymentRequest.properties

The following table provides an example set of values for the parameter file. Ensure that you enter the values specific to your configuration in the Interface, Business Unit, and Ledger fields.

Parameter Value
Program Location oracle/apps/ess/financials/payables/invoices/transactions/
Program APXPRIMPT
Interface apPaymentRequest
Business Unit 300000047498175
Accounting Date null
Oracle_Int1 1000
Source ONE_TIME_PAYMENTS
Import Set null
Purge N
Summarise N
Ledger 300000047488112
Invoice Group null
Oracle_Int2 1

Example File: apPaymentRequest.properties

This section provides an example parameter file, with the name: apPaymentRequest.properties.

The file name must be same as the value entered in the Interface field within the file, with .properties as suffix.

Example File:

ProgramLocation,Program,Interface,BusinessUnit,AccountingDate,Oracle_Int1,Source,ImportSet,Purge,Summarise,Ledger,InvoiceGroup,Oracle_Int2 /oracle/apps/ess/financials/payables/invoices/transactions/,APXPRIMPT,apPaymentRequest,300000047498175,null,1000,ONE_TIME_PAYMENTS,null,N,N,300000047488112,null,1

Definition: apPaymentRequest.csv

This file contains the payment requests that are paid through Oracle ERP Cloud. The file provided here is an example, containing the minimum set of data required to load a payment request.

Prepare this file in the .csv format, using the following structure:

Parameter Description
Transaction Identifier Identifier that uniquely identifies a payment request line record.
Party Name Name of the party to whom the payment is to be made.
Party Original System Reference Party reference from the original system or external source.
Party Type Type of party receiving the payment.
Location Original System Reference Original system reference for the party site address.
Country Location of the address by country.
Address Line 1 First line of the party address.
Address Line 2 Second line of the party address.
City City of party to whom the payment is to be made.
State State of party to whom the payment is to be made.
Province Province of party to whom the payment is to be made.
County County of party to whom the payment is to be made.
Postal Code Postal code of party to whom the payment is to be made.
Postal Plus 4 code Four-digit extension to postal code.
Addressee Person or recipient located at the address.
Global Location Number Number that uniquely identifies each global location in a trading-partner enterprise.
Language Operating language of the party-site address.
Phone Country Code Country code for the telephone number.
Phone Area Code Area code for address phone number.
Phone Phone number of the party receiving payment.
Phone Extension Phone extension of the party receiving payment.
Payee E-Mail address E-mail address of the payee to whom the payment is to be made.
Party Tax Registration Number Unique identifier assigned to a party or party site by a tax authority.
Account Country Code Country code of the bank account of payee.
Account Currency Code Currency code of the bank account of payee.
Account Number Bank account number of the payee.
IBAN International Bank Account Number of payee.
Secondary Account Reference Account number reference such as Building Society Roll Number.
Account Suffix One to three digit number at the end of the account number that determines the kind of account.
Account Name Name of the payee bank account.
Account Type Code Type of bank account, such as SAVINGS and CHECKING.
Bank Name Name of the payee bank to whom the payment is to be made.
Bank Number Number of the payee bank to whom the payment is to be made.
Bank Branch Name Name of the branch of the payee bank.
Bank Branch Number Number of the bank branch. This is known as the routing transit number in US and Canada.
BIC SWIFT or Bank Identification Code (BIC) of the bank branch.
Check Digits One or more digits used to validate a bank account number.
Account Description Description of the payee bank account.
Account Alternate Name Alternate account name of the bank account of payee.
Business Unit Sold-to business unit name for which the transaction is being created.
Source Name of the external system from which a payment request is imported.
Invoice Number Unique number for payment request.
Invoice Date Date on which payment request is submitted.
Currency Currency of the payment request.
Description Information describing the invoice created for payment.
Import Set Name or number assigned to the import set containing payment requests.
Legal Entity The name of the legal entity that belongs to your own corporate structure (enterprise).
Payment Terms Name of the payment terms used to create installments and to calculate due dates, discount dates, and discount amounts for each invoice.
Payment Method Code identifying the payment method.
Pay Group Used to group payment requests into a single pay run.
Liability Combination Account to which liability is recorded in accrual basis of accounting.
Line Number The number of the invoice line.
Amount The invoice line amount to import for payment.
Line Description Line description.
Distribution Combination Account combination used for creating invoice distribution.

Example Values: apPaymentRequest.csv

The following table provides an example set of values for the payment-request file.

Parameter Value
Transaction Identifier 100109
Party Name Fred Jones
Party Original System Reference OTP-PTY-100109
Party Type ORGANIZATION
Location Original System Reference OTP-PTY-100109
Country GB
Address Line 1 Oracle Parkway
Address Line 2 Thames Valley Park
City Reading
State UK
Province N/A
County N/A
Postal Code RG6
Postal Plus 4 code 1RA
Addressee N/A
Global Location Number N/A
Language N/A
Phone Country Code N/A
Phone Area Code N/A
Phone N/A
Phone Extension N/A
Payee E-Mail address fred.jones@emaildomain.com
Party Tax Registration Number N/A
Account Country Code GB
Account Currency Code GBP
Account Number 99453336
IBAN N/A
Secondary Account Reference N/A
Account Suffix N/A
Account Name F Jones
Account Type Code N/A
Bank Name HSBC
Bank Number N/A
Bank Branch Name Reading
Bank Branch Number 754322
BIC N/A
Check Digits N/A
Account Description N/A
Account Alternate Name N/A
Business Unit UK Business Unit
Source ONE_TIME_PAYMENTS
Invoice Number OTP-FJONES-109
Invoice Date 2021/11/09
Currency GBP
Description Refund
Import Set GBP,Refund,,UK Legal
Legal Entity UK Legal Entity
Payment Terms Immediate
Payment Method N/A
Pay Group N/A
Liability Combination 303.30.2210.000.000.000
Line Number 1
Amount 20
Line Description One time payment lines
Distribution Combination 303.30.7740.000.000.000