Setup in Oracle E-Business

You will need to complete the following steps to use the Oracle E-Business integration.

Set up SFTP

First, you will need to set up a shared folder for your company to exchange files with Fairmarkit. This folder can be in a location you share or on Fairmarkit Files (files.fairmarkit.com). SFTP configuration works similarly to a shared cloud drive where you can upload or download files. Fairmarkit will work with you to configure SFTP.

Within your company folder, there will be a folder for each environment (Staging and Production), and each folder will contain the following subfolders:

  • archived: Optional folder that stores files that are no longer needed.
    Example: production/archived/
  • from_fairmarkit: Fairmarkit will upload files you need to process back to Oracle E-Business (e.g., award files).
    Example: production/from_fairmarkit/
  • to_fairmarkit: You will upload files that Fairmarkit will need to process (e.g., purchase requisitions, purchase orders, and suppliers).
    Example: production/to_fairmarkit/
  • logs: Stores output logs from inbound and outbound processes.
    Example: production/log/

Create an export on Oracle E-Business

For each data set you want to send to Fairmarkit, you will need to create a custom program/PL-SQL package to export CSV flat files.

You will use the following Oracle E-Business primary records to export data:

Suppliers

  • ap_suppliers
  • ap_supplier_sites_
  • ap_supplier_contacts hz_parties

Requisitions

  • po_requisition_header_all
  • po_requisition_lines_all

Purchase Orders

  • po_headers_all
  • po_lines_all
  • po_line_locations_all
  • po_distributions_all

Schedule your export from Oracle E-Business

You will need to create a schedule to export your flat files to the SFTP location you set up with Fairmarkit. Typically, you'll be able to export based on conditions (e.g., a new requisition or supplier is created) in the application or on a scheduled basis (e.g., every 3 hours).

Create an import to Oracle E-Business

There are two options for importing your data from Fairmarkit into Oracle E-Business.

Option 1: Use the Requisition Import Program

Oracle E-Business imports your files into custom staging tables using a custom program. The data is imported directly into the tables you exported the data from or into the interface tables (l_req_hdr, l_req_line_tbl, and l_req_dist_tbl).

Note: Optionally, you can build a program to validate requisition updates before they are imported into the interface tables.

Finally, Oracle E-Business will run the Requisition Import to update requisitions with data from the interface tables.

Option 2: Use Oracle API - PO_REQUISITION_UPDATE_PUB

Oracle E-Business uses a custom program to import your files into custom staging tables - Application object Library.

  • Header - XX_REQ_HEADERS_ALL
  • Line - XX_REQ_LINES_ALL
  • Distributions - XX_REQ_DSITRIBUTIONS_ALL

Oracle E-Business runs the Oracle PO_REQUISITION_UPDATE_PUB API to validate data in staging tables and update requisitions. During the process, it completes the following steps:

  1. Moves data into interface tables for validation:
    • l_req_hdr
    • l_req_line_tbl
    • l_req_dist_tbl
  2. Updates the requisition from interface tables:
    • po_requisition_update_pub.req_hdr
    • po_requisition_update_pub.req_line_tbl
    • po_requisition_update_pub.req_dist_tbl

  3. If needed, you can also reinitialize workflow: P_Submit_approval = Y

    PO_REQUISITION_UPDATE_PUB.update_requisition
    (
    p_init_msg_list => 'T'
    ,p_commit => 'Y'
    ,x_return_status => l_return_status
    ,x_msg_count => l_msg_count
    ,x_msg_data => l_msg_data
    ,p_submit_approval => 'N'
    ,p_req_hdr => l_req_hdr
    ,p_req_line_tbl => l_req_line_tbl
    ,p_req_dist_tbl => l_req_dist_tbl

For more information on the export format from Fairmarkit, review the template or see Export requests for quote.