Procurement module
Purchase invoice lines
This unified layer is applied to the pipeline containing data for purchase invoice line transactions (also called Receipt lines). Purchase price variance (PPV), Purchase Invoice Amount and Quantity can be mapped here.
Columns table
Column Name | Data Type | Description | Key |
A/P Invoice | Text (50) | The unique identifier code associated with the purchase invoice header. | Primary key |
Account code | Text (50) | The unique identifier code associated with the account against which the general ledger transaction is recorded. | |
Actual purchase price | Numeric | The actual purchase price of the item. | |
Company | Text (50) | The unique identifier code associated with the company. | Additional key |
Cost amount | Numeric | Numeric column that shows the cost amount. | |
Department | Text (50) | The unique identifier code for the department. | |
Freight amount | Numeric Text | Numeric value representing the amount charged for freight on the invoice. | |
Incoming/Outgoing | Text (8) | Flag column that indicates whether the transaction is a return or not. | |
Invoice Amt incl. Tax and Freight | Numeric | Numeric value representing the total amount the item was purchased for, including tax and freight charges. | |
Invoice date | Date | The date associated with the purchase invoice. | |
Invoice discount amount | Numeric | Numeric value indicating the total discount amount for this invoice line. | |
Invoice line amount | Numeric | The purchase amount associated with the invoice line, excluding tax and freight. | |
Invoiced Qty | Numeric | This represents the number of items that are on the purchase invoice line. | |
Item | Text (50) | The unique identifier code associated with the item that has been purchased. | |
Item group | Text (50) | The unique identifier code of the group that the item belongs to. | |
Item key | Text (100) | For use in Tableau/Power BI relationship mappings. This is usually made up of the concatenation of foreign key columns with the company column of a measure group pipeline. If the dimension that the measure group is relating to is not a per company dimension, a concatenation with a blank value will be used. | Concatenated key |
Line Amount in std price | Numeric | The total line amount, calculated as Standard cost * Quantity. | |
Payment Term | Text (50) | Unique identifier code of the terms of payment of the invoice. | |
PPV amount | Numeric | Purchase price variance corresponding to the deviation of the standard cost price for the current item. | |
Purchaser | Text (50) | Unique identifier code of the employee or buyer associated with the purchase invoice. | |
Purchaser key | Text (100) | For use in Tableau/Power BI relationship mappings. This is usually made up of the concatenation of foreign key columns with the company column of a measure group pipeline. If the dimension that the measure group is relating to is not a per company dimension, a concatenation with a blank value will be used. | Concatenated key |
Return amount | Numeric | Numeric column that shows the amount of a returned invoice line. | |
Returned quantity | Numeric | Numeric column that shows the quantity of a returned invoice line. | |
Segment 1 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 1 refers to the first segment in the list. | |
Segment 2 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 2 refers to the second segment in the list. | |
Segment 3 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 3 refers to the third segment in the list. | |
Segment 4 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 4 refers to the forth segment in the list. | |
Segment 5 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 5 refers to the fifth segment in the list. | |
Standard price | Numeric | The standard price of the item that has been purchased. | |
Storage location | Text (50) | The unique identifier code of the warehouse, site or storage location associated with the purchase invoice. | |
Storage location key | Text (100) | For use in Tableau/Power BI relationship mappings. This is usually made up of the concatenation of foreign key columns with the company column of a measure group pipeline. If the dimension that the measure group is relating to is not a per company dimension, a concatenation with a blank value will be used. | Concatenated key |
Tax amount | Numeric | Numeric column showing the tax amount associated with the invoice line. | |
Vendor | Text (50) | The unique identifier code of the supplier or vendor associated with the invoice. | |
Vendor group | Text (50) | The unique identifier code of the group that the vendor belongs to. | |
Vendor key | Text (100) | For use in Tableau/Power BI relationship mappings. This is usually made up of the concatenation of foreign key columns with the company column of a measure group pipeline. If the dimension that the measure group is relating to is not a per company dimension, a concatenation with a blank value will be used. | Concatenated key |
Relationships
Name | Related layer | Mapping |
Company | Company | Company -> Company |
Items | Items | Item key -> Item key |
Employee | Employee | Purchaser key -> Sales employee key |
Storage locations | Storage locations | Storage location key -> Storage location key |
Vendors | Vendors | Vendor key -> Vendor key |
Note
Credit memos and return transactions should be included in the pipeline that is mapped to this layer. It is assumed that all amount columns mapped to this unified layer are already currency converted to the Main currency. If amounts are not currency converted when they are mapped, cross-company reporting may not work as expected.
Purchase order lines
The Purchase order lines unified layer is applied to the Purchase order lines pipeline, which contains information about purchase orders such as order amount, quantity and dates. Delivery performance metrics can be derived from the columns in this unified layer.
Columns table
Column Name | Data Type | Description | Key |
Actual Delivery Date | Date | Date column representing the date on which delivery took place. In the context of purchasing, this means the date that the item was received at the warehouse. | |
Company | Text (50) | The unique identifier code associated with the company. | Additional key |
Days to receive | Integer | Numeric column showing the number of days between actual receipt date and the expected receipt date if it has been delivered, or between today's date and the expected receipt date if it has not been delivered. This is used to indicate whether the order was late or not. | |
Days to receive bucket | Integer | A column that shows the number of days past due for days between 150 days early and 150 days late. It can be used to relate to the aging buckets pipeline. | |
Delivery fulfillment | Text (50) | This column indicates whether the purchase order has been delivered in full, partially or not at all. It is used to calculate the Perfect order index (POI) metric. | |
Delivery precision | Text (50) | This column indicates whether the purchase order has been delivered in on-time, late, early, or not at all. It is used to calculate the Perfect order index (POI) metric. | |
Delivery type key | Text (100) | For use in Tableau/Power BI relationship mappings. This is usually made up of the concatenation of foreign key columns with the company column of a measure group pipeline. If the dimension that the measure group is relating to is not a per company dimension, a concatenation with a blank value will be used. | Concatenated key |
Department | Text (50) | The unique identifier code for the department. | |
Employee | Text (50) | The unique identifier code of the employee associated with the purchase order. | |
Freight amount | Numeric | The freight amount on the purchase order line. | |
Item | Text (50) | The unique identifier code associated with the item that has been bought. | |
Item group | Text (50) | The unique identifier code for the group that the item belongs to. | |
Item key | Text (100) | For use in Tableau/Power BI relationship mappings. This is usually made up of the concatenation of foreign key columns with the company column of a measure group pipeline. If the dimension that the measure group is relating to is not a per company dimension, a concatenation with a blank value will be used. | Concatenated key |
Line amount not yet received | Numeric | Numeric column showing the currency amount associated with an order that has not yet been received, ie the outstanding amount | |
Order Qty | Numeric | The number of items that have been ordered in the purchase order line. | |
Payment term | Text (50) | The unique identifier code for the terms of payment for this purchase order. | |
Purchase line status | Text (50) | The unique identifier code that shows the current status of the purchase order line. For example, this could be the code representing values such as 'Ordered', 'In Progress', 'Complete' etc. | |
Purchase order | Text (50) | The unique identifier code of the purchase order header record that this line belongs to. Primary key | |
Purchase order – Posting date | Date | Date column representing the order date. | |
Purchase order line amount | Numeric | The purchase order amount associated with the order line. | |
Purchase order line count | Integer | A column that counts the number of purchase order lines. If the finest granularity of this table is purchase order lines, this will just be the row count. | |
Purchase order type | Text (50) | Purchase order type Text (50) The unique identifier that represents the type of the purchase order. Examples are 'Item' or 'Service' | |
Purchaser key | Text (100) | For use in Tableau/Power BI relationship mappings. This is usually made up of the concatenation of foreign key columns with the company column of a measure group pipeline. If the dimension that the measure group is relating to is not a per company dimension, a concatenation with a blank value will be used. | Concatenated key |
Remaining Open Qty | Numeric | Numeric column showing the quantity associated with an order that has not yet been received, ie the outstanding quantity | |
Segment 1 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 1 refers to the first segment in the list. | |
Segment 2 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 2 refers to the second segment in the list. | |
Segment 3 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 3 refers to the third segment in the list. | |
Segment 4 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 4 refers to the forth segment in the list. | |
Segment 5 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 5 refers to the fifth segment in the list. | |
Shipping Type | Text (50) | Shipping Type Text (50) The unique identifier code associated with the delivery method of the purchase order. For example, this could be the codes representing 'Air', 'Sea', 'Rail' etc, or even 'Express', 'Standard', 'DHL', 'UPS' etc. | |
Storage location | Text (50) | The unique identifier code of the warehouse, site or storage location associated with the purchase order. | |
Storage location key | Text (100) company | For use in Tableau/Power BI relationship mappings. This is usually made up of the concatenation of foreign key columns with the company column of a measure group pipeline. If the dimension that the measure group is relating to is not a per company dimension, a concatenation with a blank value will be used. | |
Tax amount | Numeric | Numeric column showing the tax amount associated with the order line. | |
Unit | Text (50) | Unique identifier code of the unit of measure associated with the quantity purchased. | |
Unit price | Numeric | The price per unit of the item that has been purchased. | |
Vendor | Text (50) | The unique identifier code of the supplier or vendor associated with the order. | |
Vendor group | Text (50) | The unique identifier code of the group that the vendor belongs to. | |
Vendor key | Text (50) | For use in Tableau/Power BI relationship mappings. This is usually made up of the concatenation of foreign key columns with the company column of a measure group pipeline. If the dimension that the measure group is relating to is not a per company dimension, a concatenation with a blank value will be used. | Concatenated key |
Relationships
Name | Related layer | Mapping |
Company | Company | Company -> Company |
Delivery types | Delivery types | Delivery type key -> Delivery type key |
Items | Items | Item key -> Item key |
Storage locations | Storage locations | Storage location key -> Storage location key |
Vendors | Vendors | Vendor key -> Vendor key |
Note
It is assumed that all amount columns mapped to this unified layer are already currency converted to the Main currency. If amounts are not currency converted when they are mapped, cross-company reporting may not work as expected.