Coupa P2P Purchase-to-Pay

Extraction
- System configuration
- Extraction tool configuration
Input data
- Input types
- Input tables & fields
Configuring transformations
- Dbt variables
Design specifications

Extraction

System configuration

The data extraction is developed for Coupa.

The January 2023 Release (R35) is used as the basis. It utilizes API endpoints that should be forward and backward compatible.

Environmental settings

In order to connect to Coupa, one must use OAuth 2.0. Details on what is required are written below, but please also use the Coupa Documentation on getting started for further clarification.

To set up your Coupa test instance with a new connection, go to Setup > Oauth2/OpenID Connect Clients. You can type ‘oauth’ in the Search box to find it fast.
Click on Create. For Grant Type select: Client credentials. Specify a Name for the Client, Login, Contact info, and Contact Email.
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'

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.
The above settings are needed to setup the extraction script in the next section. Read below how to configure the extractor_settings.env file to include these settings.

After following these steps the Coupa API should be accessible.

Extraction tool configuration

This app template includes a Python API Extractor, which connects to the Coupa source system and extracts the necessary tables and fields to CSV files.

In order to use this successfully, please follow these steps:

Make sure you have Python installed on your system. If not, download and install Python from the official website.
Download the coupa_p2p_extractor.zip file and extract it to a directory of your choice.
Install the libraries listed in the requirements.txt file. It is recommended to create a virtual environment in which you can install them.
Save the required information in a .env file for the Python Extractor (one is included and currently titled extractor_settings.env) to use in connecting to Coupa:

url = "https://{organization-name}.coupahost.com/"
Name = 'Your name for this client'
Login = 'Your login'
Contact_Firstname = 'Your contact first name'
Contact_Lastname = 'Your contact last name'
Contact_Email = 'Your contact email'
client_id = 'Your client ID'
client_secret = 'Your client secret'
scopes = The Scopes from above Replace all of the above with the actual values provided for the Coupa API connection.

Add start and end dates in the specified format to the extractor_settings.env script.
Enter a target directory for the CSV files to write to.
Open a command prompt or terminal, navigate to the directory where the coupa_p2p_extractor.py file is located.
Run the script by executing the following command in the terminal: python coupa_p2p_extractor.py
Let the script run. It will take some time.
Upload the CSVs to the app using the DataUploader. More information can be found on the UiPath Documentation Portal.

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
invoice_lines
invoices_source
purchase_order_lines
purchase_order_revisions
purchase_orders_source
receiving_transactions
requisition_lines
requisitions
suppliers
users

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

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

Datetime format: The datetime format is 'YYYY-MM-DD HH24:MI:SS.FF3 TZHTZM', which is also listed in the dbt_project.yml file. Examples are: start_date = '2022-01-01T00:00:00+03:00' end_date = '2023-06-05T00:00:00+03:00'
Target directory: The target directory for the CSV output should be formatted with forward slash. Example: ./testing_data/

Known limitations

Get Requests: Coupa Get Requests only return 50 records per call, which means that the Python Extractor may take longer to pull all necessary records in the defined date range.
Coupa Access Tokens: Coupa Access Tokens only last for 24 hours, so will need to be refreshed in order to run again.