This app template uses SAP Ariba as source system. Extraction for the source system is done using a custom extractor, which extracts data from SAP Ariba in CSV format using Ariba APIs. Both full data sets and delta extraction are supported. The generated files can be loaded using CData Sync. Make sure you have a valid license for CData Sync and have installed CData Sync.
This app template is built for SAP Ariba v.2208 or newer. 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. Instructions on how to set up the APIs is available in the documentation of the extractor.
An extractor is developed in Python that allows to extract data from SAP Ariba in CSV format using Ariba APIs. Note that due to limitations of the Ariba APIs regarding the number of requests and the number of records extracted per request, this extraction might take longer than expected.
For the extractor and its documentation, download the SAP_Ariba_extractor.zip. Follow the instructions in the documentation to install and configure the extractor. Execute the extractor for all P2P models.
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 [companycodes] SELECT * FROM [companycodes.csv];
REPLICATE [countries] SELECT * FROM [countries.csv];
REPLICATE [paymentterms] SELECT * FROM [paymentterms.csv];
REPLICATE [purchasegroups] SELECT * FROM [purchasegroups.csv];
REPLICATE [purchseorgs] SELECT * FROM [purchseorgs.csv];
REPLICATE [suppliers] SELECT * FROM [suppliers.csv];
REPLICATE [uoms] SELECT * FROM [uoms.csv];
REPLICATE [users] SELECT * FROM [users.csv];
REPLICATE [Vendor_List] SELECT * FROM [Vendor_List.csv];
REPLICATE [Invoice_ApprovalRecords] ([Invoice_Header_id] VARCHAR(255), [Date] VARCHAR(255), [TimeUpdated] VARCHAR(255), PRIMARY KEY ([Invoice_Header_id]), [TimeUpdated] DATETIME) SELECT * FROM [Invoice_ApprovalRecords.csv];
REPLICATE [Invoice_ApprovalRequests] ([Invoice_Header_id] VARCHAR(255), [ActivationDate] VARCHAR(255), [ManuallyAdded] VARCHAR(255), [RuleName] VARCHAR(255), PRIMARY KEY ([Invoice_Header_id]), [TimeUpdated] DATETIME) SELECT * FROM [Invoice_ApprovalRequests.csv];
REPLICATE [Invoice_Header] ([Invoice_Header_id] VARCHAR(255), [CompanyCode] VARCHAR(255), [CreateDate] VARCHAR(255), [InvoiceDate] VARCHAR(255), [InvoiceNumber] VARCHAR(255), [InvoiceOrigin] VARCHAR(255), [InvoiceSourceDocument] VARCHAR(255), [InvoiceSubmissionMethod] VARCHAR(255), [MaxDiscountApplicable_ApproxAmountInBaseCurrency] VARCHAR(255), [PaymentTerms] VARCHAR(255), [ResubmitDate] VARCHAR(255), [ScheduledPaymentDate] VARCHAR(255), [SubmitDate] VARCHAR(255), [Supplier_Name] VARCHAR(255), [TotalInvoicedLessTax_ApproxAmountInBaseCurrency] VARCHAR(255), PRIMARY KEY ([Invoice_Header_id]), [TimeUpdated] DATETIME) SELECT * FROM [Invoice_Header.csv];
REPLICATE [Invoice_LineItems] ([Invoice_Header_id] VARCHAR(255), [Invoice_Item_id] VARCHAR(255), [Amount_ApproxAmountInBaseCurrency] VARCHAR(255), [Description_Description] VARCHAR(255), [Description_SupplierPartNumber] VARCHAR(255), [Description_UnitOfMeasure] VARCHAR(255), [Order] VARCHAR(255), [OrderLineNumber] VARCHAR(255), [Quantity] VARCHAR(255), [ShipTo_Name] VARCHAR(255), [TaxDetail_Description] VARCHAR(255), PRIMARY KEY ([Invoice_Header_id]), PRIMARY KEY ([Invoice_Item_id]), [TimeUpdated] DATETIME) SELECT * FROM [Invoice_LineItems.csv];
REPLICATE [InvoiceReconciliation_Header] ([InvoiceReconciliation_Header_id] VARCHAR(255), [ApprovedDate] VARCHAR(255), [CreateDate] VARCHAR(255), [Invoice] VARCHAR(255), [PaidDate] VARCHAR(255), [RejectedDate] VARCHAR(255), [ResubmitDate] VARCHAR(255), [StatusString] VARCHAR(255), [SubmitDate] VARCHAR(255), PRIMARY KEY ([InvoiceReconciliation_Header_id]), [TimeUpdated] DATETIME) SELECT * FROM [InvoiceReconciliation_Header.csv];
REPLICATE [InvoiceReconciliation_Payments] ([InvoiceReconciliation_Header_id] VARCHAR(255), [UniqueName] VARCHAR(255), [NetDueDate] VARCHAR(255), PRIMARY KEY ([UniqueName]), [TimeUpdated] DATETIME) SELECT * FROM [InvoiceReconciliation_Payments.csv];
REPLICATE [Order_Header] ([Order_Header_id] VARCHAR(255), [ApprovedDate] VARCHAR(255), [CompanyCode] VARCHAR(255), [CreateDate] VARCHAR(255), [IsServiceOrder] VARCHAR(255), [ManuallyOrdered] VARCHAR(255), [Name] VARCHAR(255), [OrderMethodCategory] VARCHAR(255), [Requester] VARCHAR(255), [ResubmitDate] VARCHAR(255), [StatusString] VARCHAR(255), [Supplier] VARCHAR(255), [Supplier_Name] VARCHAR(255), PRIMARY KEY ([Order_Header_id]), [TimeUpdated] DATETIME) SELECT * FROM [Order_Header.csv];
REPLICATE [Order_LineItems] ([Order_Header_id] VARCHAR(255), [Order_Item_id] VARCHAR(255), [Amount_ApproxAmountInBaseCurrency] VARCHAR(255), [BillingAddress_City] VARCHAR(255), [BillingAddress_Country] VARCHAR(255), [Description_CommonCommodityCode_Name] VARCHAR(255), [Description_Description] VARCHAR(255), [Description_SupplierPartNumber] VARCHAR(255), [Description_UnitOfMeasure] VARCHAR(255), [EndDate] VARCHAR(255), [NeedBy] VARCHAR(255), [NumberConfirmedAccepted] VARCHAR(255), [NumberOnReq] VARCHAR(255), [Quantity] VARCHAR(255), [Requisition] VARCHAR(255), [ShipTo_Lines] VARCHAR(255), [ShipTo_Name] VARCHAR(255), [TaxDetail_FormulaString] VARCHAR(255), PRIMARY KEY ([Order_Header_id]), PRIMARY KEY ([Order_Item_id]), [TimeUpdated] DATETIME) SELECT * FROM [Order_LineItems.csv];
REPLICATE [Order_SplitAccountings] ([Order_Header_id] VARCHAR(255), [Order_Item_id] VARCHAR(255), [CostCenter_CostCenterDescription] VARCHAR(255), [TimeUpdated] DATETIME, PRIMARY KEY ([Order_Header_id])) SELECT * FROM [Order_SplitAccountings.csv];
REPLICATE [Receipt_ApprovalRequests] ([Receipt_Header_id] VARCHAR(255), [ActivationDate] VARCHAR(255), [ManuallyAdded] VARCHAR(255), [ReportingReason] VARCHAR(255), [RuleName] VARCHAR(255), PRIMARY KEY ([Receipt_Header_id]), [LastModified] DATETIME) SELECT * FROM [Receipt_ApprovalRequests.csv];
REPLICATE [Receipt_Header] ([Receipt_Header_id] VARCHAR(255), [ApprovedDate] VARCHAR(255), [Order] VARCHAR(255), [Preparer] VARCHAR(255), PRIMARY KEY ([Receipt_Header_id]), [TimeUpdated] DATETIME) SELECT * FROM [Receipt_Header.csv];
REPLICATE [Receipt_LineItems] ([Receipt_Header_id] VARCHAR(255), [Receipt_Line_id] VARCHAR(255), [Comment] VARCHAR(255), [Date] VARCHAR(255), [LineItem_NumberInCollection] VARCHAR(255), PRIMARY KEY ([Receipt_Header_id]), PRIMARY KEY ([Receipt_Line_id]), [TimeUpdated] DATETIME) SELECT * FROM [Receipt_LineItems.csv];
REPLICATE [Requisition_ApprovalRecords] ([Requisition_Header_id] VARCHAR(255), [Comment_Text] VARCHAR(255), [Date] VARCHAR(255), [RealUser] VARCHAR(255), [User] VARCHAR(255), PRIMARY KEY ([Requisition_Header_id]), [TimeUpdated] DATETIME) SELECT * FROM [Requisition_ApprovalRecords.csv];
REPLICATE [Requisition_Header] ([Requisition_Header_id] VARCHAR(255), [CreateDate] VARCHAR(255), [IsServiceRequisition] VARCHAR(255), [Preparer] VARCHAR(255), [Requester] VARCHAR(255), [StatusString] VARCHAR(255), PRIMARY KEY ([Requisition_Header_id]), [TimeUpdated] DATETIME) SELECT * FROM [Requisition_Header.csv];
REPLICATE [Requisition_History] ([id] VARCHAR(255), [action] VARCHAR(255), [date] DATETIME, [passwordAdapter] VARCHAR(255), [realUser] VARCHAR(255), [requisitionId] VARCHAR(255), [summary] VARCHAR(255), [uniqueName] VARCHAR(255), [name] VARCHAR(255), PRIMARY KEY ([id])) SELECT * FROM [Requisition_History.csv];
REPLICATE [Requisition_LineItems] ([Requisition_Header_id] VARCHAR(255), [Requisition_Item_id] VARCHAR(255), [Amount_ApproxAmountInBaseCurrency] VARCHAR(255), [Description_Description] VARCHAR(255), [Description_CommonCommodityCode_Name] VARCHAR(255), [Description_ShortName] VARCHAR(255), [Description_UnitOfMeasure] VARCHAR(255), [PurchaseGroup] VARCHAR(255), [PurchaseOrg] VARCHAR(255), [Quantity] VARCHAR(255), [TaxDetail_FormulaString] VARCHAR(255), PRIMARY KEY ([Requisition_Header_id]), PRIMARY KEY ([Requisition_Item_id]), [TimeUpdated] DATETIME) SELECT * FROM [Requisition_LineItems.csv];
The SAP Ariba extractor supports incremental extraction. With incremental extraction, only the data that has changed since the last extraction is extracted. This will reduce the amount of data that needs to be extracted and thus the extraction time.
The steps to configure incremental extraction are described below. You are advised to configure the incremental extraction as an additional extraction configuration, to make sure that you can also perform a full extraction as needed. See also section limitations.
Please note that when using Automation Cloud, you will need an additional staging database to store data and merge incremental data with the existing data. See Incremental extraction prerequisites in Automation Cloud for more information. This also implies that the CData Sync job for full extractions will need to be configured to use the staging database as destination.
To configure incremental extraction in the extractor, you can use the --delta argument. This requires that a full extraction has been done before. You are also advised to use a different output location for incremental extracts. Please see the README file that is included with the extractor for more details on how to specify those arguments.
To configure incremental extraction in CData Sync, you will need to configure an additional job to merge the incremental extracted data into the existing dataset. You can use the same steps described above to setup a new job in CData Sync, but with the following changes:
When using Automation Cloud with a staging database, you will need an additional CData Sync job to load the data from the staging database into the Process Mining platform. Make sure the source connection is the staging database. To configure the destination connection, see Create a destination connection (Automation Cloud).
The following tables include the list of fields per input table, their description and data type to be used.
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 |
The following tables are extracted from the source system:
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 |
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 |
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 |
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 |
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 |
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.
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 |
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 |
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 |
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.
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 |
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 |
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 |
Contains purchasing organizations information.
| Field | Type | Description |
|---|---|---|
| Name | text | The name of the responsible purchasing organization |
| UniqueName | text | Unique identifier of the purchasing organization |
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 |
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 |
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 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 |
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 |
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 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.
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 |
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 |
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 |
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 |
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 |
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 app template.
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 app template 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 app template. And it can also be used for reviewing the attributes assigned to each activity. To populate this model, the transformations must run once.
Ariba does not provide the setups for payment terms through any API. Thus, there is an automated logic in the app template 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.
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). |
| 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. |
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:
Invoice_ID from Invoice_header is used both as Invoice ID and the Accounting document ID.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. |
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.
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 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.
Furthermore SAP Ariba can generate new versions of documents. For example, when a Purchase Requisition is changed and approved, a new version of the Purchase Order is created. This new version will have a new ID and will be considered as a new document in the model. When using incremental extraction, the old version is not deleted from the outputted data set.
You are advised to perform a full extraction from time to time to make sure that the data is consistent and up to date.