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.
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.
'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'
After following these steps the Coupa API should be accessible.
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:
requirements.txt
file. It is recommended to create a virtual environment in which you can install them.extractor_settings.env
and update the following variables:Set API endpoint and credentials
(replace with values provided for the Coupa API connection):
Extraction Variables
:
coupa_p2p_extractor.py
file is located.python coupa_p2p_extractor.py
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:
requirements.txt
file. It is recommended to create a virtual environment in which you can install them.extractor_settings.env
and update the following variables:Set API endpoint and credentials
(replace with values provided for the Coupa API connection):
Database connection credentials
:
sqlserver credentials
:
Extraction Variables
:
coupa_p2p_extractor.py
file is located.python coupa_p2p_extractor.py
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.
To create the Job in CData make sure to follow the steps below.
In the Advanced tab in the Job Settings panel, define the following settings:
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"/>
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;
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 |
The following tables are extracted from the source system:
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
Contains user details.
Field | Type | Description |
---|---|---|
id |
text | User unique identifier |
fullname |
text | Full name of the user |
department_name |
text | User department name |
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) |
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 |
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. |
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 |
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. |
Extracted data is based on the following API Endpoints:
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.
dbt_project.yml
file. Examples are:
start_date = '2022-01-01T00:00:00+03:00'
end_date = '2023-06-05T00:00:00+03:00'./testing_data/