Coupa P2P Purchase-to-Pay

Extraction

This app template uses Coupa January 2023 Release (R35) or higher as source system. It utilizes API endpoints that should be forward and backward compatible. Extraction for the system is done by exporting CSV files and load this data using CData Sync. Make sure you have a valid license for CData Sync and have installed CData Sync.

Note: The extractor supports writing the extracted data to SQL Server directly, which can be used when using Automation Suite. This functionality is described in the section Extract to SQL Server. In this case the use of CData Sync is not required.

System configuration

Enable OAuth 2.0

In order to connect to Coupa, OAuth 2.0 must be used. Details on what is required are described below, use the Coupa Documentation for further clarification.

  1. To set up your Coupa instance with a new connection, go to Setup > Oauth2/OpenID Connect Clients. You can type ‘oauth’ in the Search box to find it fast.
  2. Click on Create. For Grant Type select: Client credentials. Specify a Name for the Client, Login, Contact info, and Contact Email.
  3. Choose the following scopes for the API setup. Scopes are like a set of permissions set on the API key.
'core.accounting.read core.accounting.write core.approval.configuration.read core.approval.configuration.write core.approval.read core.approval.write core.budget.read core.budget.write core.business_entity.read core.business_entity.write core.catalog.read core.catalog.write core.comment.read core.comment.write core.common.read core.common.write core.contract.read core.contract.write core.contracts_template.read core.contracts_template.write core.easy_form_response.approval.write core.easy_form_response.read core.easy_form_response.write core.easy_form.read core.easy_form.write core.expense.read core.expense.secure.read core.expense.secure.write core.expense.write core.financial_counterparty.read core.financial_counterparty.write core.global_navigation.read core.integration.read core.integration.write core.inventory.adjustment.read core.inventory.adjustment.write core.inventory.asn.read core.inventory.asn.write core.inventory.balance.read core.inventory.common.read core.inventory.common.write core.inventory.consumption.read core.inventory.consumption.write core.inventory.cycle_counts.read core.inventory.cycle_counts.write core.inventory.pick_list.read core.inventory.pick_list.write core.inventory.receiving.read core.inventory.receiving.write core.inventory.return_to_supplier.read core.inventory.return_to_supplier.write core.inventory.transaction.read core.inventory.transfer.read core.inventory.transfer.write core.invoice.approval.bypass core.invoice.approval.write core.invoice.create core.invoice.delete core.invoice.read core.invoice.write core.item.read core.item.write core.legal_entity.read core.legal_entity.write core.notifications_summary.read core.notifications_summary.write core.object_translations.read core.object_translations.write core.order_header_confirmations.read core.order_header_confirmations.write core.order_pad.read core.order_pad.write core.pay.charges.read core.pay.charges.write core.pay.payment_accounts.read core.pay.payments.read core.pay.payments.write core.pay.statements.read core.pay.statements.write core.pay.virtual_cards.read core.pay.virtual_cards.write core.payables.allocations.read core.payables.allocations.write core.payables.expense.read core.payables.expense.write core.payables.external.read core.payables.external.write core.payables.invoice.read core.payables.invoice.write core.payables.order.read core.payables.order.write core.project.read core.project.write core.punchout_site.read core.punchout_site.write core.purchase_order.read core.purchase_order.write core.requisition.read core.requisition.write core.revision_record.read core.sourcing.pending_supplier.read core.sourcing.pending_supplier.write core.sourcing.read core.sourcing.response.award.write core.sourcing.response.read core.sourcing.response.write core.sourcing.write core.supplier_information_sites.read core.supplier_information_sites.write core.supplier_information_tax_registrations.delete core.supplier_information_tax_registrations.read core.supplier_information_tax_registrations.write core.supplier_sharing_settings.read core.supplier_sharing_settings.write core.supplier_sites.read core.supplier_sites.write core.supplier.read core.supplier.risk_aware.read core.supplier.risk_aware.write core.supplier.write core.translation.read core.translation.write core.uom.read core.uom.write core.user_group.read core.user_group.write core.user.read core.user.write email login offline_access openid profile'
  1. Click on Save. Saving the client gives you values of the client Identifier and Secret which are needed to gain access to the API Scopes you have defined for it.
    Click Show/Hide to display and copy the Secret.
  2. The above settings are needed to setup the extraction script in the next section.

After following these steps the Coupa API should be accessible.

Extract CSV files

The Python API Extractor included with this app template connects to the Coupa source system and by default extracts the necessary tables and fields to CSV files.

In order to use the extractor, follow these steps:

  1. Make sure you have Python installed on your system. If not, download and install Python from the official website.
  2. Download the coupa_p2p_extractor.zip file and extract it to a directory of your choice.
  3. Install the libraries listed in the requirements.txt file. It is recommended to create a virtual environment in which you can install them.
  4. Open the file extractor_settings.env and update the following variables:
  1. Open a command prompt or terminal, navigate to the directory where the coupa_p2p_extractor.py file is located.
  2. Run the script by executing the following command in the terminal: python coupa_p2p_extractor.py
  3. Let the script run. It will take some time.
  4. Upload the CSVs to the app using CData Sync, described in the next section.

Extract to SQL Server

The Python API Extractor included with this app template can also write the extracted data directly to SQL Server. This functionality can only be used when using Automation Suite. When using this method, the following sections on CData Sync are not applicable.

In order to use the extractor, follow these steps:

  1. Make sure you have Python installed on your system. If not, download and install Python from the official website.
  2. Download the coupa_p2p_extractor.zip file and extract it to a directory of your choice.
  3. Install the libraries listed in the requirements.txt file. It is recommended to create a virtual environment in which you can install them.
  4. Open the file extractor_settings.env and update the following variables:
  1. Open a command prompt or terminal, navigate to the directory where the coupa_p2p_extractor.py file is located.
  2. Run the script by executing the following command in the terminal: python coupa_p2p_extractor.py
  3. Let the script run. It will take some time. The data will be available in the SQL Server database.
  4. Load the data into the app by using the API URL as described here: Retrieving the API Url

Setting up the source connection in CData Sync

Setting up the destination connection in CData Sync

To set up the destination connection, follow the steps as defined in Create a destination connection (Automation Cloud). Note: If you are using Automation Suite, make sure to select AUTOMATION SUITE as the DELIVERY option and select the correct version of Automation Suite you are using.

Creating the extraction job in CData Sync

To create the Job in CData make sure to follow the steps below.

Advanced job settings

In the Advanced tab in the Job Settings panel, define the following settings:

Setting up the data ingestion call

After the data has been successfully loaded, the Process Mining platform needs to start the data ingestion process. This is done by calling the End of Upload API. In order to set this up, follow the steps below:

Step Action
1 Go to the Events tab in the Job Settings panel of the job you are creating
2 Edit the Post-Job Event section to add the code displayed below after <!-- Code goes here -- >. Do not modify the api:info details that are shown by default.
3 Fill out the End of Upload API with the value provided.
4 In case you need a debugging log for this call, uncomment the two lines provided in the code below and provide a location for the log file.
5 Click on Save Changes. 
<api:set attr="http.url" value="END_OF_UPLOAD_API"/> 
<!-- <api:set attr="http.verbosity" value="5"/> -->
<!-- <api:set attr="http.logfile" value="D:/mydir/cdata_log.txt"/> -->
<api:call op="httpPost" in="http"/>

Table Replication

Once the job is correctly setup, click on Add Custom Query under the Tasks tab and paste the following queries (each query needs to maintain the semicolon at the end). Make sure you save all changes.

REPLICATE [approvals] select * from approvals;
REPLICATE [invoice_lines] select * from invoice_lines;
REPLICATE [invoices_source] select * from invoices_source;
REPLICATE [purchase_order_lines] select * from purchase_order_lines;
REPLICATE [purchase_order_revisions] select * from purchase_order_revisions;
REPLICATE [purchase_orders_source] select * from purchase_orders_source;
REPLICATE [receiving_transactions] select * from receiving_transactions;
REPLICATE [requisition_lines] select * from requisition_lines;
REPLICATE [requisitions] select * from requisitions;
REPLICATE [suppliers] select * from suppliers;
REPLICATE [users] select * from users;

Input data

Input types

The following table provides an overview of the different field types and their default format settings.

Field type Description
boolean true, false, 1, 0
datetime YYYY-MM-DDTHH24:MI:SS TZH:TZM
double Decimal separator: . (dot); thousand separator: none
integer Thousand separator: none
text N/A

Input tables & fields

The following tables are extracted from the source system:

approvals

Contains approvals of different documents.

Field Type Description
id text Approval unique identifier
created_at datetime Creation date of the row
status text Status of the approval
approver_id text User identifier for approver
approvable_id text The document ID that was approved
approvable_type text The document type that was approved
position text The position in the approval chain this approval occurred
approval_date datetime The date the approval occurred
note text Reason for approval or rejection

invoice_lines

Contains invoice line items details.

Field Type Description
id text Invoice line unique identifier
invoice_id text Invoice header unique identifier
invoice_number text Invoice number
created_at datetime Authorization status type
description text Item description
line_num text Invoice line number
order_header_num text Purchase order number
order_line_id text Purchase order line identifier
order_line_num text Purchase order line number
accounting_total double Accounting total of invoice line
status text Transaction status
type text Invoice line type
uom_name text Unit of measurement name
quantity double Invoice line quantity

invoices

Contains invoice header details.

Field Type Description
id text Invoice header unique identifier
created_at datetime Creation date of the row
invoice_date datetime Date of invoice
delivery_number text Delivery number
delivery_date datetime Date of supply
status text Invoice status
discount_due_date datetime Discount Due Date calculated base on the discount payment terms
net_due_date datetime Net Due Date calculated based on the net payment terms
discount_percent double Discount percent
payment_method text Payment method
invoice_number text Invoice number
created_by_id text User identifier for invoice creator
created_by_fullname text Full name of invoice creator
custom_fields_posting_date datetime Posting date of the invoice
paid boolean Indicator of invoice being paid
pay_invoice_id text Invoice payment unique identifier
pay_invoice_created_at datetime Creation date of the invoice payment
pay_invoice_status text Invoice payment status
payment_notes text Notes included with payment for invoice
payment_term_code text Payment term code
payment_term_days_for_net_payment integer Net payment days
payment_term_days_for_discount_payment integer Days for discount payment
payment_term_discount_rate double Discount payment rate
clearance_document text Clearance document attachment file name

purchase_order_lines

Contains purchase order line details.

Field Type Description
id text Purchase order line unique identifier
created_at datetime Creation date of the row
order_header_id text Purchase order header unique identifier
order_header_number text Purchase order number
line_num text Purchase order line number
quantity double Purchase order line quantity
status text Transaction status
accounting_total double Purchase order line accounting total
need_by_date datetime Need by date
requester_id text Requester user unique identifier
created_by_id text User identifier for invoice creator
commodity_name text Commodity name
uom_name text Unit of measurement name
description text Item description
requisition_line_id text Requisition line unique identifier
account_name text Account associated with the purchase order line

purchase_order_revisions

Contains purchase order revision details.

Field Type Description
id text Purchase order header identifier
revision integer Purchase order revision number
revision_id text Purchase order revision unique identifier
created_at datetime Creation date of the row
created_by text User identifier for revision creator
order_line_id text Purchase order line identifier
accounting_total_from text Accounting total of previous revision
accounting_total_to text New accounting total
status_from text Status of previous revision
status_to text New status
version_from text Version of previous revision
version_to text New version

purchase_orders

Contains purchase order header details.

Field Type Description
id text Purchase order header unique identifier
created_at datetime Creation date of the purchase order
po_number text Purchase order number
status text Purchase order status
created_by_id text User identifier for purchase order creator
created_by_fullname text Full name of purchase order creator
internal_revision integer Internal revision number
change_type text Last type of change on the purchase order
requisition_header_id text Requisition header unique identifier
ship_to_address_business_group_name text Ship-to business group name
supplier_id text Supplier unique identifier
supplier_name text Supplier name

receiving_transactions

Contains receiving transaction details.

Field Type Description
id text Receiving transaction unique identifier
created_at datetime Creation date of the receiving transaction
quantity double Receipt quantity
status text Receiving transaction status
comments text Comments
order_line_id text Purchase order line identifier
created_by_id text User identifier for creator

requisition_lines

Contains requisition line details.

Field Type Description
id text Requisition line unique identifier
line_num text Requisition line number
created_at datetime Creation date of the requisition line
description text Item description
receipt_required boolean Receipt required
source_type text Source type of purchase requisition line
currency_code text Requisition line currency code
status text Transaction status
total double Price total of the requisition line
quantity double Requisition line quantity
commodity_name text Name of the commodity for the requisition line
commodity_category text Category of the commodity for the requisition line
uom_name text Unit of measurement name
created_by_id text User identifier for requisition line creator
created_by_fullname text Full name of requisition line creator

requisitions

Contains requisition header details.

Field Type Description
requisition_id text Requisition header unique identifier
created_at datetime Creation date of the requisition header
requested_by_id text User identifier of the requisition requester
requested_by_fullname text Full name of the requisition requester
requisition_line_id text Requisition line unique identifier
status text Requisition header status

suppliers

Contains supplier details.

Field Type Description
id text Requisition header unique identifier
display_name text Supplier display name
one_time_supplier boolean One-time supplier indicator
primary_address_state text Supplier state/region
primary_address_country_name text Supplier country

users

Contains user details.

Field Type Description
id text User unique identifier
fullname text Full name of the user
department_name text User department name

Configuring transformations

Seed files

Automation_estimates_raw

This seed file is used to add automation-related properties to each activity, used for the automation potential dashboard. For more information, see Simulating Automation Potential.

Field Type Description
Activity Text Display name for the activity
Event_cost Double Cost associated with the activity
Event_processing_time Integer Processing time associated with the activity (in milliseconds)

Due_dates_configuration_raw

This seed file is used to define properties for the due dates. For more information, see Due Dates.

Field Type Description
Due_date Text The name of the due date
Due_date_type Text The Due date type
Fixed_costs Boolean An indication whether costs are fixed or time based
Cost Double Fixed costs: The amount of costs. Variable costs: The amount of costs per Time and Time_type
Time Integer A number indicating the amount of time in case of time-based costs
Time_type Text Type of time period for cost calculations. This can be any of the following values: day, hour, minute, second or millisecond

Dbt variables

Variable Type Description
automated_users text Set of User IDs that should be considered as automated.
date-format text Date format used. The date format is dependent on the database used.
datetime-format text Datetime format used. The datetime format is dependent on the database used.

Design specifications

Entities

The following table shows all entities that are used for the process.

Entity Input Data
Purchase requisitions Requisition_lines
Purchase orders Purchase_orders_source, Suppliers
Purchase order items Purchase_order_lines, Purchase_orders_source
Goods receipt Receiving_transactions
Invoices Invoices_source
Invoice items Invoice_lines
Accounting documents Invoices_source
Payments Invoices_source

Activities

Activity Entity Description
Create Purchase Requisition Item Purchase requisition The purchase requisition item is created, based on the creation date of the document.
Approve Purchase Requisition Level: {level} Purchase requisition Approval of purchase requisition, level is based on the position of the approval.
Create Purchase Order Purchase order The purchase order is created, based on the creation of the document.
Approve Purchase Order Level: {level} Purchase order Approval of purchase order, level is based on the position of the approval.
Create Purchase Order Item Purchase order item The purchase order item is created, based on the creation of the document.
Change Purchase Order Item Price Purchase order item Purchase order item price is changed, based on the Accounting_total_from and Accounting_total_to fields.
Change Purchase Order Item Status Purchase order item Purchase order item status is changed, based on the Status_from and Status_to fields.
Change Purchase Order Item Version Purchase order item Purchase order item version is changed, based on the Version_from and Version_to fields.
Create Goods Receipt Goods receipt The goods receipts is created, based on the creation date of the document.
Create Invoice Invoice The invoice is created, based on the creation date of the document.
Approve Invoice Level: {level} Invoice Approval of invoice, level is based on the position of the approval.
Create Invoice Item Invoice item The invoice item is created, based on the creation of the document.
Create Payment Payment The payment is created, based on the creation of the document.

Design details

API Endpoints

Extracted data is based on the following API Endpoints:

Adding fields to extraction

In order to add new fields to the connector, add these fields to the coupa_p2p_extractor.py script included in the extraction zip-file. Under the section that lists the fields for all input tables, you are able to edit these fields to include additional attributes for each of these API endpoints. These follow the logic of JSON notation, and one must be mindful of how the field is nested in the JSON response.

Common issues with extractor settings

Known limitations