SAP Ariba Purchase-to-Pay

Extraction

System configuration

The source system for which this connector was built is SAP Ariba v.2208. SAP Ariba is a Software as a Service (SaaS) and it gets constant updates that are deployed quarterly, naming its versions with the last two digits of the year directly followed by the month of the release (i.e. August 2022 release: v.2208).

Buying & Invoicing (procure-to-pay solution) is considered one single module and is mandatory when using this app template. All other tables that could possibly be used (for master data purposes) are part of the backbone of any SAP Ariba implementation and they were added to the transformations.

The user who sets up the APIs in SAP Ariba Developer Portal should be administrator of the system and have DSC (Designated Support Contact) permissions.

Extraction tool configuration

An extractor is developed in Python that allows to extract data from SAP Ariba in CSV format using Ariba APIs. For the extractor and its documentation, download the SAP_Ariba_extractor.zip.

Input data

The following tables include the list of fields per input table, their description, data type to be used when formatting the input and the filter flag to identify those that are being used to filter data.

Input types

Below is an overview of the different field types and their default format settings.

Field type Description
boolean true, false, 1, 0
datetime yyyy-mm-ddThh:mm:ssZ
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:

companycodes

Stores company codes and their descriptions. A company code represents an independent accounting unit within a client.

Field Type Description
Description text Name of the company
UniqueName text Unique identifier of the company

countries

Information of the countries and their descriptions.

Field Type Description
Name_en text Language dependant name of the country
UniqueName text Unique identifier of the country

Invoice_ApprovalRecords

This table stores every invoice approval done by the users involved on its approval flow.

Field Type Description
Date datetime Date of the approval
Invoice_Header_id text Unique identifier of the invoice

Invoice_ApprovalRequests

Each approval request for an invoice will generate a record in this table. Any request submitted in the system might be subject to approval. The approval rules determine which users or groups are added to the approval flow. Active approvers must approve the request in order to move it to the next approver in the approval chain.

Field Type Description
ActivationDate datetime The date when the current approver becomes active
Invoice_Header_id text Unique identifier of the invoice
ManuallyAdded boolean Field that indicates if the approval request is done manually or automatically
RuleName text Name of the rule predefined in the approval workflow

Invoice_Header

Stores invoices header information, such as amount, supplier of the invoice and its status in the process.

Field Type Description
CompanyCode text Unique identifier of the company associated to the invoice
CreateDate datetime Date on which the invoice was created
InvoiceDate datetime Date of the invoice
Invoice_Header_id text Unique identifier of the invoice
InvoiceNumber text Number of the invoice
InvoiceOrigin text The origin of the invoice (i.e. supplier)
InvoiceSourceDocument text The source document of the invoice. The invoices could be Non-PO or PO based
InvoiceSubmissionMethod text Indicates who submitted the invoice (i.e. the supplier or an internal user)
MaxDiscountApplicable_ApproxAmountInBaseCurrency double Maximum discount applicable to the invoice in base currency
PaymentTerms text Unique identifier of the payment terms associated to an invoice
ResubmitDate datetime Date on which the invoice was resubmitted
ScheduledPaymentDate datetime Date on which the payment is scheduled to be done
SubmitDate datetime Date on which the invoice was submit and sent to approval
Supplier_Name text Name of the supplier
TotalInvoicedLessTax_ApproxAmountInBaseCurrency double Total value of the invoice without taxes in base currency

Invoice_LineItems

Contains invoice line level information such as the line type, amount and tax values.

Field Type Description
Amount_ApproxAmountInBaseCurrency double Monetary value of the invoice item in base currency
Description_Description text Description of the invoiced line item
Description_SupplierPartNumber text Supplier number of the invoice item
Description_UnitOfMeasure text Unit of measurement description
Invoice_Header_id text Invoice unique identifier
Invoice_Item_id text Line item number
Order text Unique identifier of the purchase order
OrderLineNumber integer Purchase order line number
Quantity double Quantity invoiced
ShipTo_Name text Name of the plant where the items are shipped
TaxDetail_Description text Tax automatic classification. It must be null. Any not null value will be excluded (see note below)

Note: Ariba adds to each invoice line a new record to populate with all attributes related to taxation. These records are not required for the model, thus, they are being filtered out.

InvoiceReconciliation_Header

Stores invoice reconciliation document header information.

Field Type Description
ApprovedDate datetime Date when the invoice reconciliation was approved
CreateDate datetime Date when the invoice reconciliation was created
Invoice text Invoice unique identifier
InvoiceReconciliation_Header_id text Invoice reconciliation unique identifier
PaidDate datetime Date on which the invoice was paid
RejectedDate datetime Date on which the invoice reconciliation was rejected
ResubmitDate datetime Date on which the invoice reconciliation was resubmitted
StatusString text Status of the invoice reconciliation in the process
SubmitDate datetime Date on which the invoice reconciliation was submitted

InvoiceReconciliation_Payments

This table stores payment information. In SAP Ariba, when the invoice reconciliation document is fully approved, the payment requests are automatically approved and the payment process begins.

Field Type Description
InvoiceReconciliation_Header_id text Invoice reconciliation unique identifier
NetDueDate datetime The date on which the payment is due
UniqueName text Payment unique identifier

Order_Header

Purchase order header information, such as total amount and status of the document in the process.

Field Type Description
ApprovedDate datetime Date on which the purchase order was approved
CompanyCode text Unique identifier of the company associated with the purchase order
CreateDate datetime Date on which the purchase order was created
IsServiceOrder boolean Flag that indicates if the purchase order is classified as a service
ManuallyOrdered boolean Indicates if the purchase order was manually sent to the supplier
Name text User-friendly name of the purchase requisition associated to the purchase order
Order_Header_id text The unique identifier of the purchase order
OrderMethodCategory text Indicates the ordering method associated to the purchase order
Requester text Name of the requester originator of purchase order
ResubmitDate datetime Date on which the purchase order was resubmitted
StatusString text Status of the purchase order in the process
Supplier text Supplier unique identifier
Supplier_Name text Name of the supplier associated to the purchase order

Order_LineItems

Stores purchase order lines information.

Field Type Description
Amount_ApproxAmountInBaseCurrency double Rounded amount of the purchase order line in base currency
BillingAddress_City text City of the company associated to the purchase order line
BillingAddress_Country text Country of the company associated to the purchase order line
Description_CommonCommodityCode_Name text Name of the categorization of the item
Description_Description text Description of the order line item
Description_SupplierPartNumber text Unique identifier of the unit of measure associated to the purchase order
Description_UnitOfMeasure text Unique identifier of the unit of measure associated to the purchase order
EndDate datetime Indicates the end date of a purchase order line
NeedBy datetime The date by which the ordered goods or services must be received by the requester
NumberConfirmedAccepted double Quantity marked as confirmed by the supplier
NumberOnReq integer Indicates the Purchase requisition line associated to the order item
Order_Header_id text Unique identifier of the purchaser order
Order_Item_id text Purchase order line item number
Quantity double Quantity ordered
Requisition text Unique identifier of the purchase requisition associated
ShipTo_Lines text Location of the plant where the items are shipped
ShipTo_Name text Name of the plant where the items are shipped
TaxDetail_FormulaString text It must be null. Any not null value will be excluded (see note below)

Note: Ariba adds to each purchase order line a new record to populate with all attributes related to taxation. These records are not required for the model, thus, they are being filtered out.

Order_SplitAccountings

Purchase order split line items information. Split accounting in SAP Ariba is used to spread the cost of an item across multiple accounting groups.

Field Type Description
CostCenter_CostCenterDescription text Name of the cost center associated to the purchase order line item
Order_Header_id text Unique identifier of the Purchase order
Order_Item_id text Purchase order line number

paymentterms

Master data table that stores payment terms and its description.

Field Type Description
Description_en text The description of the payment term
UniqueName text Unique identifier of the payment term

purchasegroups

Purchasing groups information.

Field Type Description
Name text The name or number of the responsible purchasing group
UniqueName text Unique identifier of the purchasing group

purchseorgs

Contains purchasing organizations information.

Field Type Description
Name text The name of the responsible purchasing organization
UniqueName text Unique identifier of the purchasing organization

Receipt_ApprovalRequests

Approval requests information for the receipts, used when receipts are configured as documents subject of approval.

Field Type Description
ActivationDate datetime The date when the current approver becomes active
ManuallyAdded boolean Field that indicates if the approval request is done manually or automatically
Receipt_Header_id text Unique identifier of the receipt
ReportingReason text Reason an approver or watcher is added
RuleName text Name of the rule predefined in the approval workflow

Receipt_Header

Receipts header level information.

Field Type Description
ApprovedDate datetime Date on which the receipt is approved
Order text Unique identifier of the purchase order associated
Preparer text Name of the user who prepared the receipt
Receipt_Header_id text Unique identifier of the receipt

Receipt_LineItems

Contains information of the receipt lines.

Field Type Description
Comment text Comment entered by the user who receipt the items
Date datetime Date on which the receipt line is created
LineItem_NumberInCollection text A sequence number that identifies a line item relative to the purchase order line associated to the receipt line
Receipt_Header_id text Unique identifier of the receipt
Receipt_Line_id text Unique identifier of the receipt line

Requisition_ApprovalRecords

Requisition approvals information.

Field Type Description
Comment_Text text Comment entered by the approver
Date datetime Date when an approver approved or denied the requisition
RealUser text Name of user who actually performed the action
Requisition_Header_id text Unique identifier of the purchase requisition
User text Unique name of the user who actually performed the action

Requisition_Header

Stores requisition header information such as requester, creation date and document status in the process.

Field Type Description
CreateDate datetime Date on which de purchase requisition is created
IsServiceRequisition boolean Field which indicates if the document is a service requisition type
Preparer text Name of the user who prepared the purchase requisition
Requester text Name of the originator of requisition on behalf of whom it was created
Requisition_Header_id text Unique identifier of the purchase requisition
StatusString text Status of the purchase requisition in the process

Requisition_History

Contains historical information of purchase requisitions, purchase orders and receipt documents.

Field Type Description
action text Indicates the action taken on the document
date datetime Date on which the action is taken
id text Unique identifier of the historical record
name text Name of the user who performed the action
passwordAdapter text Indicates the type of password adapter of the user who performed the action (i.e. ThirdPartyUser)
realUser text Name of user who actually performed the action
requisitionId text Unique identifier of the purchase requisition
summary text Details of the action taken
uniqueName text Unique identifier of user who performed the action

Requisition_LineItems

Requisition lines information such as amount, requested item and its quantity.

Field Type Description
Amount_ApproxAmountInBaseCurrency double Rounded amount of the purchase requisition line in base currency
Description_CommonCommodityCode_Name text Name of the categorization of the item
Description_Description text Description of the requisition line item
Description_ShortName text Description details of the requisition line item
Description_UnitOfMeasure text Unique identifier of the unit of measure associated to the purchase requisition
PurchaseGroup text Unique identifier of the purchasing group
PurchaseOrg text Unique identifier of the purchasing organization
Quantity double Quantity of the requisition item
Requisition_Header_id text Unique identifier of the purchase requisition
Requisition_Item_id text Number of the purchase requisition line
TaxDetail_FormulaString text It must be null. Any not null value will be excluded (see note below)

Note: Ariba adds to each invoice line a new record to populate with all attributes related to taxation. These records are not required for the model, thus, they are being filtered out.

suppliers

Stores suppliers master data information.

Field Type Description
IsOneTimeVendorShell boolean Indicates whether the supplier is a one-time vendor
UniqueName text Unique identifier of the supplier

uoms

Master data table that contains information about units of measure.

Field Type Description
Description_en text Description of the unit of measure
UniqueName text Unique identifier of the unit of measure

users

Contains users related master data information.

Field Type Description
Name_en text Language dependant name of the user
PasswordAdapter text Indicates the type of password adapter of the user who performed the action (i.e. ThirdPartyUser)
UniqueName text Unique identifier of the user

Vendor_List

Contains supplier information such as address details.

Field Type Description
Address_City text City associated to the supplier
Address_Country_Code text Unique identifier of the country associated to the supplier
Address_Postal_Code text Postal code associated to the supplier
ERP_Vendor_ID text Unique identifier of the supplier

Configuring transformations

Seed files

Setup_activities_raw

This seed file has two purposes. One is to populate the activity properties that cannot be obtained from any Ariba table. The other purpose is to be the filter for activities included in the Requisition_history table. It means that any activity not available in the Setup_activities_raw file will not be present in the connector.

Note: this seed file will only be used to populate activities related to Purchase Requisitions and Purchase Orders. For this reason, it is important to populate it only with activities related to these two entities.

Field Type Description
Entity text The name of the entity that the activity belongs to. The valid values are PR or PO.
Action text The activity/status that happens in the system. For example, Submitted.
Activity_Name text The name of the activity that overwrites the Action field if the analyst chooses to. For example Submit purchase requisition.
Activity_Category text The type of activity that takes place. The valid values are Change, Set block, Remove block or null.
Activity_code text A code for the activity that takes place.
Activity_Order integer The number that defines in which order activities are executed in case they have the same event end.
Automated_flag boolean Flag that defines automated activities.

The connector comes with the Setup_activities_raw seed file pre-populated for a standard implementation of Ariba. Should the analyst find that they want to include extra activities, they just need to include them in the seed file.

This file has an auxiliary model Setup_activities_auxiliary that can be used for checking activities that are currently being excluded from the connector. And it can also be used for reviewing the attributes assigned to each activity. To populate this model, the transformations must run once.

Setup_payment_terms_raw

Ariba does not provide the setups for payment terms through any API. Thus, there is an automated logic in the connector that does an approximation to populate Discount_percentage_1 and Discount_period_1 fields in the Invoices_base model. Should the analyst or the business wants to have an exact calculation of all payment terms fields, they must use Setup_payment_terms_raw seed file.

Note: to use this alternative, the variable manual_payment_terms in the dbt_project.yml should be set to true.

To gather the information, the analyst may request the business to use the following instructions to extract the PaymentTermsConsolidated.csv file directly from the SAP Ariba environment.

  1. Log in into the SAP Ariba Child Environment
  1. Go to Manage and click on Core Administration
  1. Once in the Core Administration section, click on Data Import/Export option inside the Site Manager menu and go to its Export tab.
  1. Copy in the search filters option the text 'Payment Terms (Consolidated File)' and click on search. Then, click on the Export button.
  1. Select 'All' option and click on 'Ok'
  1. The file will be downloaded to your computer.

This manual version of the logic only accepts Ariba's Discount_type = 'percent'. The analyst must select records where the discount type is percent. Once the file is downloaded, the information should be added to the csv file as follows as stored in the PaymentTermsConsolidated.csv file:

Field Type Description
Payment_terms text The UniqueName field. For example PT1.
Discount_period_1 double The PayInDays field of the first discount (per UniqueName).
Discount_percentage_1 double The Discount field of the first PayInDays period (per UniqueName).
Discount_period_2 double The PayInDays field of the second discount applicable (per UniqueName).
Discount_percentage_2 double The Discount field of the second PayInDays period (per UniqueName).
Net_payment_period double The PayInDays field the of the line where the Discount is equal to zero (per UniqueName).

Dbt variables

Variable Type Description
manual_payment_terms boolean To enable when the seed file Setup_payment_terms_raw is populated.
payment_terms_baseline_date text Defines the baseline date used for payment terms calculation.

Design specifications

Entities

The next table contains the list of all entities and the SAP Ariba input tables used to create them.

Entity Input data
Purchase requisitions Requisition_header, Requisition_line_items, Uoms
Purchase orders Order_header, Requisition_line_items, Vendor_list, Company_codes, Countries, Suppliers, Purchase_groups, Purchase_orgs
Purchase order items Order_line_items, Order_split_accountings, Order_header_input, Countries, Uoms
Goods receipt Receipt_header, Receipt_line_items
Invoices Invoice_header, Invoice_reconciliation_header, Invoice_reconciliation_payments, Company_codes, Payment_terms
Invoice items Invoice_header, Invoice_line_items, Uoms
Accounting documents Invoice_header
Payments Invoice_reconciliation_header, Invoice_reconciliation_payments

In a standard SAP Ariba implementation, the P2P process always starts with the creation of a Purchase requisition (PR), and once it gets approved, the Purchase order (PO) is created automatically by the system. This results in a one-to-one relationship between PR items and PO items.

Ariba calls Requisition_item_id to the line number in a requisition (e.g. 1, 2, 3, etc). In order to create a unique ID for the lines in the Purchase requisitions entity, the Requisition_header_id and the Requisition_item_id from the Requisition_line_items_input table are concatenated. This concatenation is also the primary key of the Requisition_LineItems transactional table in SAP Ariba.

Other considerations:

Activities

There are two approaches for creating activities:

Each approach follows a different logic.

Approach 1: Activities based on the transactional table information.

Activity Entity Description
Create purchase requisition Purchase requisitions Based on Requisition_header, Requisition_line_items
Approve purchase requisition Purchase requisitions Based on Requisition_header, Requisition_line_items, Requisition_approval_records
Approve purchase order Purchase orders Based on Order_header
Create purchase order Purchase orders Based on Order_header, Order_split_accountings
Resubmit purchase order Purchase orders Based on Order_header
Create purchase order item Purchase order items Based on Order_header, Order_line_items, Order_split_accountings
Approve goods receipt Goods receipt Based on Receipt_header, Receipt_line_items
Goods receipt Goods receipt Based on Receipt_header, Receipt_line_items.
Request goods receipt approval Goods receipt Based on Receipt_header, Receipt_line_items
Create invoice Invoices Based on Invoice_header
Request invoice approval Invoices Based on Invoice_approval_requests
Approve invoice Invoices Based on Invoice_header
Resubmit invoice Invoices Based on Invoice_header
Create invoice reconciliation Invoices Based on Invoice_reconciliation_header
Submit invoice reconciliation Invoices Based on Invoice_reconciliation_header
Approve invoice reconciliation Invoices Based on Invoice_reconciliation_header
Reject invoice reconciliation Invoices Based on Invoice_reconciliation_header
Resubmit invoice reconciliation Invoices Based on Invoice_reconciliation_header
Create invoice item Invoice items Based on Invoice_header, Invoice_line_items
Create outgoing payment Payments Based on Invoice_reconciliation_header, Invoice_reconciliation_payments

Approach 2: Activities based on the historical table information.

Activities that are present in the historical table Requisition_history are called Actions. These Actions are mostly standard across SAP Ariba. However, it is not possible to know in advance which Actions will be used across implementations. The Requisition_history table contains Actions of Purchase requisitions, Purchase orders and Goods receipt (all of them assigned to the Purchase_requisition_id in the table). Goods receipt activities were filtered out as they are generated elsewhere. Then, specific logics were designed to assign the Action to either Purchase requisitions or Purchase orders.

The most standard Actions were set up in advance in the Setup_activities_raw seed file. For new Actions, the analyst should add them following the steps described.

Activity Entity Description
Purchase requisitions Edit purchase requisition Based on Action Edited. Purchase requisition properties were changed.
Purchase requisitions Change purchase requisition Based on Action Changed. Purchase requisition line items properties were changed.
Purchase requisitions Change purchase requisition approver Based on Action Changed. An approver was deleted from the approval chain.
Purchase requisitions Resubmit purchase requisition Based on Action Resubmitted. The purchase requisition has been resubmitted.
Purchase requisitions Submit purchase requisition for approval Based on Action Submitted. The purchase requisition was submitted for approval.
Purchase orders Submit purchase order to the supplier Based on Action Ordered. The purchase order was successfully sent to the supplier.
Purchase orders Cancel purchase order Based on Action Canceled. The purchase order has been canceled.

Design details

Supporting models

Last_receipt

This supporting model was created to populate the Latest_actual_delivery_date field in the Purchase_order_items_base model. In SAP Ariba, a new receipt is created every time an item of a Purchase Order is received. This receipt will generate all the lines contained in the Purchase Order associated. Therefore, one purchase order item could be associated to multiple receipt headers. For this reason, the Lines aggregated model takes the maximum date on which a Purchase order item was received.

First_records_purchase_order_line

This supporting model was created to populate the fields Purchasing_organization and Purchasing_group of the Purchase_orders_base model. This information in the system is stored at Purchase requisition item level. The values were taken from the first line of the Purchase requisition.

Requisition_history_preprocessing

Requisition_history is the unique historical table of the model and contains 'Actions' (which represents statuses or activities) happening to the Purchase requisition, Purchase order or Goods receipts. In the Requisition_history table, all actions are associated to the Purchase_requisition_ID, even if it is happening at Purchase order or Goods Receipt instance of the process. There is a logic in this model that "reads" the 'Summary' field and seeks for a word with a structure that represents a Purchase_order_ID: PO+Number (The acronym of the Entity plus a next-number is the standard way in which SAP Ariba creates the IDs). If it finds it, then the logic populates the Purchase_order_id field with that value.

Example:

If the 'Action' does not belong to a Purchase order, it will be assigned to the Purchase requisition ID. All Actions related to Goods receipt were previously filtered out because they could not be assigned to its corresponding Goods receipt ID.